Re: Reload only specific databases from pg_dumpall

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(dot)gurjeet(at)gmail(dot)com> 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(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
>

--
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