0

I have a large PostgreSQL database on a server with a failing disk. I cannot run pg_dump because of disk errors.

I have another server with the identical OS (Ubuntu 16.04) and the identical version of PostgreSQL (9.5.6). How do I copy the raw database files to the new server? I don't care if read errors corrupt parts of the database, as I have ways of identifying and fixes those issues, but I need the data copied over before I can fix it. From what I can tell, there aren't many read errors, but even one will stop pg_dump.

There's a scant wiki page on this topic, and it suggests doing a "filesystem level copy" but makes no mention of how to actually do this.

Edit: I used rsync to copy over my data directory (/var/lib/postgresql/9.5/main) and configuration directory (/etc/postgresql/9.5/main). Running sudo service postgresql start on the new server runs without error, but trying to connect via sudo -u postgres psql returns:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

and the log shows:

2017-05-23 15:13:08 EDT [14604-1] [unknown]@[unknown] LOG:  incomplete startup packet
2017-05-23 15:13:08 EDT [14603-2] LOG:  MultiXact member wraparound protections are now enabled
2017-05-23 15:13:08 EDT [14602-1] LOG:  database system is ready to accept connections
2017-05-23 15:13:08 EDT [14608-1] LOG:  autovacuum launcher started
2017-05-23 15:13:43 EDT [14602-2] LOG:  received fast shutdown request
2017-05-23 15:13:43 EDT [14602-3] LOG:  aborting any active transactions
2017-05-23 15:13:43 EDT [14608-2] LOG:  autovacuum launcher shutting down
2017-05-23 15:13:43 EDT [14605-1] LOG:  shutting down
2017-05-23 15:13:45 EDT [14605-2] LOG:  database system is shut down
2017-05-23 21:33:29 EDT [27345-1] FATAL:  could not load server certificate file "/etc/ssl/certs/ssl-cert-snakeoil.pem": No such file or directory

What is this missing certificate and how do I fix it?

Edit: I've fixed the SSL error by following these instructions, and now all my files seem to have been transferred.

My database seems to be largely accessible. The only issue I've found is when I go to do a full vacuum, I get the error:

ERROR:  could not open file "base/106800/107273": No such file or directory

How do I fix this? I realize it's file corruption, probably caused by that file not being readable during the rsync. How do I "fill it in" with blank values so the vacuum can proceed? The error occurs when vacuuming a specific table, and the data in that table can be regenerated.

  • pg_basebackup – a_horse_with_no_name May 23 '17 at 19:45
  • To start server, you can use ../postgresql/9.5/bin/pg_ctl start -D new_data_directory . Then using psql to connect to your PG server. – Luan Huynh May 24 '17 at 2:12
2

This is the script I ended up having to write to transfer all the files, meant to be run from the destination server.

#!/bin/bash
set -i

# Ensure databases on both servers are stopped.
sudo service postgresql stop
ssh -t myuser@oldserver "sudo service postgresql stop"

# Ensure our user owns postgres's files so our rsync call can access them.
ssh -t myuser@oldserver "sudo chown -R myuser:myuser /etc/postgresql"
ssh -t myuser@oldserver "sudo chown -R myuser:myuser /var/lib/postgresql"

# Copy down all the files.
sudo rsync --progress -azv --delete myuser@oldserver:/etc/postgresql/9.5/main/ /etc/postgresql/9.5/main
sudo rsync --progress -azv --delete myuser@oldserver:/var/lib/postgresql/9.5/main/ /var/lib/postgresql/9.5/main

# Fix permissions.
ssh -t myuser@oldserver "sudo chown -R postgres:postgres /etc/postgresql"
ssh -t myuser@oldserver "sudo chown -R postgres:postgres /var/lib/postgresql"
sudo chown -R postgres:postgres /etc/postgresql
sudo chown -R postgres:postgres /var/lib/postgresql

# Fix a missing ssl cert that pg uses for connections.
cd /etc/ssl/certs
sudo make-ssl-cert generate-default-snakeoil --force-overwrite

# Bring the server back up.
sudo service postgresql start

Rsync reported some errors, but I'm browsing my databases via psql and pgadmin3 and so far everything looks correct.

The fix for the SSL error I found here.

  • 2
    rsync will almost certainly not behave correctly when you get read errors. it will either truncate files, or shorten them by omitting the unreadable data. To copy files that contain read errors on a Unix system, you should use the "dd" command with options conv=noerror conv=sync. See "man 1 dd" for details. – cliffordheath May 24 '17 at 5:36
  • @cliffordheath, When I try to read or copy the damaged file, the kernel flips the entire filesystem into read-only mode, so I'm not sure where I should read the output of dd into. In either case, if the data's corrupted, it's likely of no value anyways. Running a vacuum on the new database showed the damage was isolated to a single table, which I can regenerate, so I simply truncated the table, which reset the damage. – Cerin May 24 '17 at 13:21
  • I'm glad you were able to reconstruct your data. Obviously to use the "dd" route, you need a reliable writable disk as well as your bad one. You should never mount a bad disk as writable anyhow. Although a file might have bad blocks that will be replaced by nulls, there is still a fair chance that you could have recovered data from the rest of it (as long as the blocks don't move around!) - but that's not needed now. – cliffordheath May 25 '17 at 7:00
1

First, if you can't run pg_dump because of disk errors, copying the database will almost certainly not work.

That said, it's certainly worth trying. Mount the drive as read only. Run

SHOW data_directory;

Whatever that comes back, copy (with cp or preferably rsync) that directory to a new a disk. Run

SELECT version();

Get a version of PostgreSQL cluster working with the same major and minor version numbers. Then try to start the server and see if it works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.