CODEWITHSUNDEEP
sqldatabasemacospostgresqlpostgres.app
Steve
Steve

Reputation: 325

Import PostgreSQL dump on OSX

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!

Upvotes: 3

Views: 10335

Answers (3)

Amit Meena
Amit Meena

Reputation: 4424

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.

Upvotes: 2

Craig Ringer
Craig Ringer

Reputation: 324265

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.

Upvotes: 2

khampson
khampson

Reputation: 15296

-U <username> is correct for specifying the username with psql, but the last token is the database name here. It's interpreting that you specified no user and the database is username. Try adding the database name after, i.e. psql -U username database.

See the psql doc for more info regarding the myriad switches psql supports.

Upvotes: 0

Related Questions