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 Jun 24 '20 at 23:50 — iklinac, Jun 24 '20 at 23:50

score 4 · Answer 1 · answered Jun 24 '20 at 23:49

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 '20 at 23:49

Adrian Klaver

15,886
2
17
28

Aha, thank you. I did not know pg_dump was a program. Then regarding "/Users//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. – tucktuck9 Jun 25 '20 at 00:34
Yes whatever is after ```-f``` will be where the file is saved. There are plenty of examples at the page I linked to. – Adrian Klaver Jun 25 '20 at 00:42
Thank you!! This is the command that finally worked: sudo -u postgres pg_dump mydb > db.sql > computer password > postgres password – tucktuck9 Jun 25 '20 at 00:52

score 0 · Answer 2 · answered Jun 25 '20 at 00:56

0

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 '20 at 00:56

tucktuck9

61
2
8

1

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. – Adrian Klaver Jun 25 '20 at 01:19
Understood on pg_restore, thank you again for your help Adrian. – tucktuck9 Jun 25 '20 at 02:04

Creating a PostgreSQL database backup via psql on macOS

2 Answers2

Linked