I'm copying the data for a PostgreSQL database, that is, /var/lib/postgresql, from one server to another. The servers are identical except for the fact that one is 32bit and the other one is 64. I already copied the data once and PostgreSQL started, but, is there a way to verify the integrity of the data?
Postgresql – If PostgreSQL starts after copying data, is the data ok
migrationpostgresql
Related Question
- Postgresql – pg_upgrade unrecognized configuration parameter “unix_socket_directory”
- PostgreSQL – How to Efficiently Copy Millions of Rows from One Table to Another
- PostgreSQL – Replication with Periodic Log Shipping
- PostgreSQL 9.3 – Fix Corruption After Running pg_resetxlog
- PostgreSQL – Starting Streaming Replication Using pg_dump
- PostgreSQL 9.6 – Troubleshooting Master-Slave Replication Issues
- Postgresql – Performing an upgrade of the database
Best Answer
It is not safe to assume that the data is OK just because PostgreSQL starts. Pg does not do any kind of comprehensive verification run on the DB contents at startup. If it did it could take hours (or days or weeks for bigger DBs) to start.
It doesn't have any verification tools. The argument is that these should not be needed if the data is managed correctly. I'm coming to question that argument in the wake of the 9.3.x (x < 4) multixact corruption bugs, but building verification tools isn't a small job.
If the 64-bit server is running 32-bit PostgreSQL binaries, and they're compiled with the same options, it'll be fine.
If the 64-bit server is running 64-bit PostgreSQL binaries then you didn't copy the datadir in place, because 32-bit and 64-bit PostgreSQL data directories are not compatible and PostgreSQL will refuse to start.
My concern is about how you copied the data dir. You must stop the origin and destination PostgreSQL servers before copying if you just do a simple file system level copy. To live copy you must use one of the techniques documented in the manual -
pg_basebackup,pg_start_backup()+ rsync +pg_stop_backup()+ WAL archiving, etc.If you copied the datadir without stopping the server it might appear to be copied correctly, but it's not safe to use and may be subtly or not so subtly corrupt.