Cannot copy postgresql database to new server with error [unrecognized parameter "row_security"]

The "row_security" error is probably because you have a newer version of PostgreSQL locally than on the remote end. That's not a problem unless you are using that feature.

The "permission denied" and "user X does not exist" errors are why it is failing. You are trying to restore the database as a user that does not have permission to create a database on the remote server. Then, it can't find the relevant user, and then you haven't set up that user for remote access either.

Users are shared between databases and not copied with them.

So - you want to do something like:

  1. Log in as "postgres" on the remote server and "CREATE USER x ..."
  2. Restore the database as user "postgres" on the remote server and it should be able to set the ownership to the user you want.

If you do not want to grant remote access to your database you may want to either create a ssh tunnel (lots of examples on the internet) or dump to a file (use "-Fc" for the custom, compressed format) and copy the dump to the remote machine first.

If possible, try to run the same version of PostgreSQL on both servers. It makes things easier if they do need to communicate.

Tags:

Postgresql