Re: Reload only specific databases from pg_dumpall

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(at)event-s(dot)net> 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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ 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