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.marlowe@gmail.com> wrote:
On Feb 4, 2008 7:10 PM, Guido Neitzer <
lists@event-s.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[.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