Creating a PostgreSQL database backup via psql on macOS

Question

I'm trying to create a PostgreSQL local db dump via Terminal on macOS using psql. My next step is to upload/import my entire db to my Amazon RDS PostgreSQL db instance.

Can anyone help me with the Terminal command to create a dump file 'mydbdump.sql' for my 'mydb' database. I've sorted through existing questions, searched forums (tried similar Stack Overflow question), even Amazon RDS docs (above, link), and am having no luck with the commands from any of these docs. What am I doing wrong?

Here is what I've tried so far (and I've gotten nothing back, not even an error):

Via sudo:

sudo -u postgres psql pg_dump -Fc -o -f /Users/<my_computer>/database_backup.sql mydb

Via psql, connected to mydb (\c mydb;):

mydb-# pg_dump dbname=mydb -f mydbdump.sql

mydb-# pg_dump -U postgres -W -F t mydb > /Users/<my_computer>/database_backup.sql

mydb-# pg_dump -Fc mydb > /Users/<my_computer>/database_backup.sql

mydb-# pg_dump -U postgres mydb > database_backup.sql

mydb-# pg_dump -Fc mydb > /Users/<my_computer>/database_backup.sql

mydb-# pg_dump -U postgres -W -F t mydb > /Users/<my_computer>/database_backup.sql

For reference: I'm using Django, in settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'mydb',
        'USER': 'dbuser',
        'PASSWORD': '<MYPASSWORD>',
        'HOST': 'localhost',
        'PORT': '',
    }
}

By the way, my Django app is running successfully.

When I list all database relations in mydb:

\dt mydb;

Output:

List of relations
 Schema |            Name            | Type  | Owner  
--------+----------------------------+-------+--------
 public | auth_group                 | table | dbuser
 public | auth_group_permissions     | table | dbuser
 public | auth_permission            | table | dbuser
 public | auth_user                  | table | dbuser
 public | auth_user_groups           | table | dbuser
 public | auth_user_user_permissions | table | dbuser
 public | django_admin_log           | table | dbuser
 public | django_content_type        | table | dbuser
 public | django_migrations          | table | dbuser
 public | django_session             | table | dbuser
 public | polls_choice               | table | dbuser
 public | polls_question             | table | dbuser
(12 rows)

When I list all schemas in psql:

select s.nspname as table_schema,
       s.oid as schema_id,  
       u.usename as owner
from pg_catalog.pg_namespace s
join pg_catalog.pg_user u on u.usesysid = s.nspowner
order by table_schema;

Output:

table_schema    | schema_id |  owner   
--------------------+-----------+----------
 information_schema |     13337 | postgres
 pg_catalog         |        11 | postgres
 pg_temp_1          |     12314 | postgres
 pg_toast           |        99 | postgres
 pg_toast_temp_1    |     12315 | postgres
 public             |      2200 | postgres

you dont run pg_dump inside psql

iklinac
– iklinac

2020-06-24 23:50:05 +00:00
Commented Jun 24, 2020 at 23:50 — iklinac
– iklinac, Commented Jun 24, 2020 at 23:50

Adrian Klaver · Accepted Answer · 2020-06-24 23:49:28Z

4

First running pg_dump inside psql will not work.

Second, this:

sudo -u postgres psql pg_dump -Fc -o -f /Users/<my_computer>/database_backup.sql mydb

is a variation of the first thing. You want:


sudo -u postgres pg_dump -Fc -f /Users/<my_computer>/database_backup.sql mydb

pg_dump is a client program by itself.

NOTE: No -o. There is -O which is for no-owner.

See examples at bottom of this page:

https://www.postgresql.org/docs/current/app-pgdump.html

Lastly are you sure this doesn't get you an error?:

test(5432)=# pg_dump dbname=mydb -f mydbdump.sql;
ERROR:  syntax error at or near "pg_dump"
LINE 1: pg_dump dbname=mydb -f mydbdump.sql;

If not then I would check your logging settings in postgresql.conf.

answered Jun 24, 2020 at 23:49

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tucktuck9 Over a year ago

Aha, thank you. I did not know pg_dump was a program. Then regarding "/Users/<my_computer>/database_backup.sql" > I was specifying the path on my computer to save my .sql file, is this accurate? I ask because since your answer, I found my "pg_dump" is located here: /usr/local/bin/pg_dump.

Adrian Klaver Over a year ago

Yes whatever is after -f will be where the file is saved. There are plenty of examples at the page I linked to.

tucktuck9 Over a year ago

Thank you!! This is the command that finally worked: sudo -u postgres pg_dump mydb > db.sql > computer password > postgres password

tucktuck9 · Accepted Answer · 2020-06-25 00:56:27Z

1

This is the command that finally worked:

sudo -u postgres pg_dump mydb > db.sql

This prompted me for:

my computer password
then my postgres password

I did not need to specify any additional paths. The db.sql file automatically saved to my root directory.

answered Jun 25, 2020 at 0:56

tucktuck9

712 silver badges9 bronze badges

2 Comments

Adrian Klaver Over a year ago

Yes the computer user does the sudo and the user postgres does the dump. Just know the the above created a plain text dump file and you will need to use psql not pg_restore to restore it.

tucktuck9 Over a year ago

Understood on pg_restore, thank you again for your help Adrian.

Collectives™ on Stack Overflow

Creating a PostgreSQL database backup via psql on macOS

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related