Postgresql – How to migrate Postgres database using an intermediate file

migrationpostgresqlUbuntu

I have a PostgreSQL v9.5.23 database on an Ubuntu 16.04 server that I want to migrate to a new Postgres 12.4 installation on an Ubuntu 20.4 server.

The network pipe recommended by the Postgres docs won't work for me. The docs also unhelpfully say "Or you can use an intermediate file if you wish" without giving an example of how to do this.

This is what I've tried. On the originating server, I ran

$ sudo pg_dump -C {dbname} > ~/dumpfile.sql

to create the dump file.

On the target server, I get this error:

$ sudo -u postgres psql {dbname} < ~/dumpfile.sql
could not change directory to "/home/username": Permission denied
psql: error: could not connect to server: FATAL:  database "{dbname}" does not exist

Of course the target database does not exist; I used the -C option when creating the dumpfile so that it would be created automatically during upload, but evidently this is not automatic. I read through the man page for psql, but I didn't see a flag that would make it recognize the "create database" part of the dump file. Web searching didn't turn up anything, and naturally the Postgres documentation is useless.

How do I upload the file and have the "create database" portion recognized?

Edit

The could not change directory to "/home/username": Permission denied line is not the error. This is just something Postgres always does on my installation.

I have tried the upload command from the /tmp/ directory with 777 permissions on the file. It still does not work:

/tmp$ ls -Alh
-rwxrwxrwx 1 username group 5.1M Oct 21 11:07 dumpfile.sql
/tmp$ sudo -u postgres psql {dbname} < /tmp/dumpfile.sql
psql: error: could not connect to server: FATAL:  database "{dbname}" does not exist

Best Answer

When you are restoring a dump taken with -C, the database you specify on the command line is name of the database you connect to in order to execute the creation of the new database. Therefore it can't be the name of the new database. It seems like you already know this, you just haven't thought it through. psql is trying to connect to {dbname} because that is what you explicitly told it to do.

PostgreSQL doesn't "treat" sudo commands differently. Sudo is in charge, not PostgreSQL. Different commands do different things, and PostgreSQL is just along for the ride.

You are trying to tell the "postgres" system user to read a file located in your daily user's home directory. It probably doesn't have permission to do that. Again, this is not PostgreSQL's choice. You could put the file somewhere else (like /tmp) and give it world read permissions, or you could change your pg_hba.conf so you can connect to the database as your daily user rather than needing to sudo all the time.

Related Question