From: | "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> |
---|---|
To: | "Guido Neitzer" <lists(at)event-s(dot)net> |
Cc: | "Postgresql General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Reload only specific databases from pg_dumpall |
Date: | 2008-02-05 01:00:46 |
Message-ID: | 65937bea0802041700y1294a171sf1476bc2404561ab@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 *
Mail sent from my BlackLaptop device
From | Date | Subject | |
---|---|---|---|
Next Message | Guido Neitzer | 2008-02-05 01:10:54 | Re: Reload only specific databases from pg_dumpall |
Previous Message | Tom Lane | 2008-02-05 00:55:56 | Re: REINDEX on large DB vs. DROP INDEX/CREATE INDEX |