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@xxxxxxxxx> wrote:
Here's what I'd do. First, use head, tail, and grep to find the linesOn Feb 4, 2008 7:10 PM, Guido Neitzer <lists@xxxxxxxxxxx> 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.
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.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
