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:I cannot see a -f option on pg_dumpall. This is the command:
> What was the output format option used (-f option) ? Was it the
> plain-text (SQL) or custom format?
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
