If PostgreSQL starts after copying data, is the data ok?

Question

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?

• peterh: In your place I did a psql dump on both servers, and compared this dump. (Maybe you could move even the dumps instead of the data files, if you want to be sure.)

Answer 1

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.

• Pablo Fernandez: I don't know why it worked once, but indeed it doesn't work copying the data between architectures.