Search Postgresql Archives

Re: Reload only specific databases from pg_dumpall

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
On 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.

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux