How do I migrate Postgres database using an intermediate file

Question

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

• PiotrChernin: @LaurenzAlbe I'm not sure why Postgres would treat that as different from sudo -u postgres {command}, but I tried it and it resulted in the same "database does not exist" error
• PiotrChernin: @LaurenzAlbe I do have a postgres database on the target server. It was created at installation.
• jjanes: The page you link to does not recommend a "network pipe". Also, it is for a version many years older than the version you are using.

Answer 1

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.

• PiotrChernin: No, I had no idea that I had to connect to an existing database in order to create a completely different database. So what would the command to create a new database {dbname} look like, then?
• OrangeDog: @PiotrChernin CREATE DATABASE {dbname};
• PiotrChernin: I guess I should clarify: What would the command to import the dumpfile while creating the new database according to the CREATE DATABASE directive already included dumpfile look like?
• PiotrChernin: Also, this is not a permissions issue. Everything I've tried, I've tried from the /tmp/ directory as well. The could not change directory error is something that Postgres has always done, even on the old server, and it has never kept a command from working. The actual error is that it won't create the database despite instructions for creating the database being included in the dumpfile.
• jjanes: The database given on the command line should be one that already exists, "postgres" or "template1" would be common choices. Or since you are running it as the "postgres" system user, just don't specify a database and it will automatically use "postgres".

Answer 2

troubleshooting steps:

• make sure directory exist
• make sure you are using the right user normally we use postgres user you can switch to it using sudo -i -u postgres if you are using a sudoer user, and su - postgres if you are using root
• postgres user should have the needed permission for this directory
• postgres service needs be up and running, you can check it by running ps -ef |grep postgres or the traditional way service postgresql status

• PiotrChernin: The Postgres home directory /var/lib/postgresql/ and data directory /var/lib/postgresql/12/main/ both exist on the target server. Are you referring to one of them?
• PiotrChernin: I've tried both sudo -u postgres {command} and sudo -i -u postgres then {command} on the target server. They both give the error noted in the original post.
• Ahmad Abuhasna: @PiotrChernin this is the installation directory, connect normally to psql then hit show all; and it will show you all the needed, and tablespace can be found any where in the system depend on the creation statement, and if you have pgadmin this will save you a lot of time, this will show you exactly where is the data dir.
• Ahmad Abuhasna: the command you mentioned in the original post was for restore DB, and if you cant access the file via postgres then it will fail for sure (mentioned in the third point of the troubleshoot), if you dont want to change the permission then move the file for another directory where postgres can access it.
• PiotrChernin: It's not a permissions problem. I've checked permissions and I've tried doing everything from /tmp. The error indicates that Postgres is complaining that the database I'm telling it to create doesn't exist before creating it.