Search Postgresql Archives

Re: Reload only specific databases from pg_dumpall

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux