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 <[EMAIL PROTECTED]> wrote: > On Feb 4, 2008 7:10 PM, Guido Neitzer <[EMAIL PROTECTED]> 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 > -- > -- [EMAIL PROTECTED] [EMAIL PROTECTED] 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
