Import PostgreSQL dump on OSX

Question

I've only used MySQL before. Postgres is a little different for me. I'm trying to use the Postgres.app for OSX. I have a database dump from our development server, and I want to create the correct user roles and import the database to my local machine so I can do development at home (can't access the database remotely).

I think I've created the user. \du shows the appropriate user with the CreateDB permission. Then I used \i ~/dump.sql which seems to have imported the database. However when I use \l to list databases, it doesn't show up. So then I tried logging in with psql -U username, but then it tells me "FATAL: database username does not exist." Is that not the right switch for login? It's what the help said.

I'm getting frustrated with something simple so I appreciate any help. With the Postgres.app, how can I create the necessary users with passwords and import the database? Thanks for any help!

Answer 1

It sounds like you probably loaded the dump into the database you were connected to. If you didn't specify a database when you started psql it'll be the database named after your username. It depends a bit on the options used with pg_dump when the dump file was created though.

Try:

 psql -v ON_ERROR_STOP=1

 CREATE DATABASE mynewdb TEMPLATE template0 OWNER whateverowneruser;
 \c mynewdb
 \i /path/to/dump/file.sql

Personally, I recommend always using pg_dump -Fc to create custom-format dumps instead of SQL dumps. They're a lot easier to work with and pg_restore is a lot nicer than using psql for restoring dumps.

Answer 2

Mac users: If you are here in 2023 and are on the Mac operating system and you have created a database dump using pg_dump utility

A. The dump is taken in SQL format (simple one)

pg_dump -U <USER_NAME> -h <DATABASE_HOST> <DB_NAME> > sample.sql

Then in order to restore it use the below command.

First, create the database manually using the command line/terminal

psql  -U <USER_NAME>  -h <DATABSE_HOST>

Once connected create the database using the command

create database test;
\q

Now restore the dump using the below command and you are done :)

psql  -U <USER_NAME>  -d <DATABSE_NAME> -h <DATABSE_HOST> <  sample.sql

For localhost use 127.0.0.1 as the database host.

(B) Dump is taken in binary format.

pg_dump -U <USER_NAME> -h <DATABASE_HOST> -d <DB_NAME>  -Fc > sample.dump

Then in order to restore it use the below command.

First, create the database manually using the command line/terminal

psql  -U <USER_NAME>  -h <DATABSE_HOST>

Once connected create the database using the command

create database test;
\q

Now restore the dump using the below command.

pg_restore -Fc  -U <USER_NAME>  -d <DATABSE_NAME> -h <DATABSE_HOST>   sample.dump

For localhost use 127.0.0.1 as the database host.

Note: Binary backups are a more practical choice for backing up production databases because they provide faster, more reliable, and more consistent backups. However, text backups may still be useful in certain scenarios, such as archiving or manual inspection of the backup file.