Re: Reload only specific databases from pg_dumpall

["Gurjeet Singh" <singh.gurjeet@xxxxxxxxx>]

I understand it all needs a little bit of 'vi' wizardry, (or whichever
editor you are using). Also, as with all open-source suggestions, do
not rely on this procedure until you understand what and how it does
what it does.

Best regards,

On Feb 4, 2008 4:39 PM, Gurjeet Singh <singh.gurjeet@xxxxxxxxx> wrote:

Sorry couldn't respond earlier...

Yeah, there's no -f option to pg_dumpall, I confused it with pg_dump's -F option.

Since the output of dumpall is plain SQL, since and you would use psql to restore the DB, there's no command line option to execute only a part of the script.

Long story short: you have to manually extract the contents of your DB from the dump file.

Here's what I did: created 3 databases test{1,2,3}. Created single table in each of them. And here's what I see in the head of the dump:

REVOKE ALL ON DATABASE template1 FROM PUBLIC;
REVOKE ALL ON DATABASE template1 FROM gsingh;
GRANT ALL ON DATABASE template1 TO gsingh;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;
CREATE DATABASE test1 WITH TEMPLATE = template0 OWNER = gsingh ENCODING = 'UTF8';
CREATE DATABASE test2 WITH TEMPLATE = template0 OWNER = gsingh ENCODING = 'UTF8';
CREATE DATABASE test3 WITH TEMPLATE = template0 OWNER = gsingh ENCODING = 'UTF8';

So lets say we want to restore DB test2... here's how I would go about it:

Take that dump, remove all other 'CREATE DATABASE' commands except for the one for test2. Search for string 'test2'; I get to the following line:

\connect test2

delete everything between the a.m 'CREATE DATABASE' command and this \connect command.

Since there's another DB after test2 (we saw the order in 'CREATE DATABASE' commands, remember ), so I search for the next '\connect' command. I find this:

\connect test3

Form this line on, I delete everything from the file. And I am done.

Now I run:

 psql -p 5444 -f ~/08-02-04.sql -d postgres

And my test2 DB is resored.

psql -p 5444 -f ~/08-02-04.sql -d test2 -c "select count(*) from test;"
 count
-------
   100
(1 row)

HTH,

Best regards,

On Feb 4, 2008 10:54 AM, Guido Neitzer <lists@xxxxxxxxxxx> wrote:

On 04.02.2008, at 10:41, Gurjeet Singh wrote:

> What was the output format option used (-f option) ? Was it the
> plain-text (SQL) or custom format?

I cannot see a -f option on pg_dumpall. This is the command:

pg_dumpall > `date "+%y-%m-%d"`.sql

I just want to use an older file from a dump to restore a server, but
I don't want to reload all databases (because that will take way
longer).

cug

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

17° 29' 34.37"N,   78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N,   73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device

-- 
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

17° 29' 34.37"N,   78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N,   73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device