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

Question

I'm novice in PostgreSQL. I've succesfully created database, set "philipyoung"—my ID as super user, and use the command below, to copy local database to new server in elephantsql.

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

and then the error below happens

SET
SET
SET
SET 
SET
ERROR: unrecognized configuration parameter "row_security"
ERROR: permission denied to create database
ERROR: role "philipyoung" does not exist
FATAL: no pg_hba.conf entry for host "xxx.xxx.xxx.xx", user "xxxx", database "xxxx", SSL on

any help would be appreciated

Answer 1

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.