How to backup Discourse database from command line

database
backup
postgresql

(Dmitry Fedyuk) #1

You can backup your forum database using standard PostgreSQL pg_dump tool.

Step 1.

Set up access to Discourse database.

Step 2.

Install PostgreSQL pg_dump tool.

Of course, PostgreSQL is already installed inside Docker container’s virtual machine, but it is convenient to access the database without diving into the virtual machine.

For dumping database, you need only to install PostgreSQL client tools, not a server.
On Debian / Ubuntu PostgreSQL client tools can be installed by command:

aptitude install postgresql-client

Step 3.

Set up authentication for pg_dump.
In contract to mysqldump, you can not specify your database password as pg_dump parameter.
I recommend to create .pgpass file in your home directory with your PostgreSQL credentials.
It is a plain text file with single line:

localhost:*:discourse:postgres:<password>

You have already got a password if you followed the instruction How to access Discourse database.
Insert it in .pgpass file in place of <password>.
Set access permissions for .pgpass file to 0600 (it means read / write access for you and no access for others).

Step 4.

Run pg_dump:

pg_dump --clean --create --schema=public --no-owner --no-privileges --host=localhost --port=<port> --username=postgres discourse > <filename>.sql

Set your database port in place of <port> (you should set it in app.yml).
Set your backup file name in place of <filename>