[Klug-general] Postgresql backup and restoration

[MacGyveR]

On Tuesday 26 January 2010, Karl Buckland wrote:
> Hi all,
>
> A Postgresql specific question here as it's driving me crazy. I have a
> moderately large (6Gb) Postgresql database running 8.1.10. I'm
> currently trying to copy the database to another server and rename it.
> I'm using pg_dump to dump the database specifically, then I'm piping
> the output to sed to rename the database and finally piping that
> output to psql to insert the new database. From what I can see, this
> appears to be the standard way to do things?
>
> The structure of the database seems to be fine, with table
> definitions, foreign keys, indices, triggers, views, functions, etc,
> all copied. However a lot of the data isn't getting copied. The errors
> seem to indicate that it's because of the triggers and foreign keys.
> For example, Postgres is trying to add data for table a that has a
> foreign key for table b, before it has imported the data for table b.
> Having used MySQL's dump and import I haven't experienced this issue.
>
> The Postgresql docs
> (http://www.postgresql.org/docs/8.1/static/populate.html) suggest
> removing the foreign key constraints, but helpfully don't tell you
> how. I don't really want to have to remove the foreign key constraints
> manually for all tables and then re-add them as there are 430 tables.
> Is it possible to disable the constraints whilst importing (which is
> what MySQL must do)?
>
> Does anyone have any suggestions? I'm afraid migrating to MySQL (where
> this stuff seems to just work!) isn't a valid suggestion (yet!).
>
> Karl
>
> _______________________________________________
> Kent mailing list
> Kent at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/kent

are you using pgdump_all, even though this will dup other db's too the 
database you require can be "cut" out of the file (an editor better than vi is 
required if the dump file is greater than 2gb) ? i would bother using sed as 
you can easily rename a database once it has been restored if you are 
restoring onto another system. also have you tried using the --disable-
triggers option. you could also try the -d or -D options but this will be very 
slow. also when piping into psql you can set the verbosity level to echo every 
line and stop on an error, so you know exactly where the problem is.

-- 
--------------------------------
http://www.thedumbterminal.co.uk