Re: Reload only specific databases from pg_dumpall

Nice...

Can this be reliably put into a script? The script would take only the DB
name as parameter. And one gotcha I woud look out for is to see the CREATE
DB's template= is nothing but template0, else warn the user that the DB may
not be reliably restored.

Best regards,

On Feb 4, 2008 6:03 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:

> On Feb 4, 2008 7:10 PM, Guido Neitzer <lists(at)event-s(dot)net> wrote:
> > On 04.02.2008, at 18:00, Gurjeet Singh wrote:
> >
> > > 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.
> >
> > Sure, the only problem is, I'm talking about a laaaarge file. Maybe I
> > just import the whole bunch and drop some of the databases after that.
> > Seems to be less hazzle, but takes probably an hour or two longer, so
> > I'll take a service window next night ...
> >
> > Thanks for the hints.
>
> Here's what I'd do. First, use head, tail, and grep to find the lines
> you need to cut at...
>
> for instance, the create database statements will come up first, so
> something like this:
>
> For a test from dumpall I did this:
>
> $ grep -in "Create database" test.sql
> 34:CREATE DATABASE smarlowe WITH TEMPLATE = template0 OWNER = postgres
> ENCODING = 'LATIN1';
>
> That pulls the create database line out. Then, I can find the dump
> data with this:
>
> $ grep -in \\connect test.sql
> 5:\connect postgres
> 38:GRANT CONNECT ON DATABASE template1 TO PUBLIC;
> 41:\connect postgres
> 141:\connect smarlowe
> 335:\connect template1
>
> So, for the smarlowe database I need to get lines 141 to 334. Quick test:
>
> head -n 141 test.sql |tail -n 1
> \connect smarlowe
> head -n 335 test.sql|tail -n 1
> \connect template1
>
> So, the lines are where They should be and head / tail lets me grab
> them. Now, to grab the bits I need:
>
> echo $((335-141))
> 194 -- Number of lines to grab
>
> So, to grab the dump, I can use
>
> $ head -n 334 test.sql|tail -n 194 > smarlowe.sql
>
> Note I'm grabbing 335-1 since I don't actually want the \connect template1
> bit.
>
> Quick test:
> $ head -n 334 test.sql|tail -n 194|head -n 1
> \connect smarlowe
> $ head -n 334 test.sql|tail -n 194|tail -n 4
> --
> -- PostgreSQL database dump complete
> --
>

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