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 test2delete 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 test3Form 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 postgresAnd 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@event-s.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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB
http://www.enterprisedb.com17° 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.netMail sent from my BlackLaptop device
