PostgreSQL backup\restore problem

[Jon]

Hi All,

I currently have an application for asset tracking\software license tracking in an test environment and all is working fine.

I have come to the point today where I want to move it to a production server and am having some issues.

The installed version of Apache\Postgres, schema and all passwords are the same on both boxes.

I have gone into PGAdmin III and connected to the DB server in the test environment and run a backup of the pre-production DB. When i copy the files over to the new production box and restore the database it throws quite alot of errors and seems to have part of the modified schema missing and some data\user entries.

Does anyone have any ideas what the problem may be? Is there a better way of executing the DB backup and restore on Postgres? I'm pretty new to PostgreSQL and this has been driving me nuts for days.

TIA.

Cheers,

Jon.

[techieboy]

Are you missing some compile time functions (assuming you've compiled it yourself on a *nix box) or other functions later added to the db templates (template0 and template1) and therefore the test or production db by default when they are being created?

Was the new database created with the same character set as the test db?

What options are you using for the db backup/dump? Have you tried to dump it including insert statements (makes the reload slower but can help with compatibility issues)?

Might be worth downloading the trial version of EMS DB Comparer to document any differences (and replicate settings, if you wish). They make what I think are the best SQL utilities available for PostgreSQL (and MySQL).

I only really use command line access when creating/dumping/restoring db's and usually the following commands to dump/reload

pg_dump -Ft db_name > dump_file_name

pg_restore -d new_db_name dump_file_name