You can backup your databases that are managed by Appliku through the dashboard.

They can be stored on the server or uploaded to S3-like storage.

If you want to use S3 to store your backups read how to create S3 bucket with write only access

How to backup Postgres database

In order to backup a Postgres database in Appliku:

  • Open the dashboard
  • Go to your application that has a database
  • Click on the "Manage" link in the "Databases" card:
  • Pick you database
  • Click on the "Add backup" link
  • On the new page specify parameters for the backup:
  • After submitting the form you will be taken back to the database page. It will show your new backup task added:

Backup postgres database command line

The pg_dump command used to perform backups looks like this:

pg_dump  --no-privileges --no-owner --no-acl <postgresql_url> 

Here is what these pg_dump arguments are for:

  1. --no-privileges: This argument tells pg_dump to exclude any privileges (i.e., permissions) that are granted to users or roles for the database objects in the dump file. This means that when you restore the dump file, the privileges will not be restored.
  2. --no-owner: This argument tells pg_dump to exclude any ownership information for the database objects in the dump file. This means that when you restore the dump file, the ownership will not be restored.
  3. --no-acl: This argument tells pg_dump to exclude any access control lists (ACLs) that are defined for the database objects in the dump file. This means that when you restore the dump file, the ACLs will not be restored.

psql backup database to file

To store a postgres backup into a file we use this command:

pg_dump  --no-privileges --no-owner --no-acl <postgresql_url>  | gzip > filename.tar.gz

This command performs the pg_dump then compresses text with the gzip command and sends all output to a file.

how to backup postgres database in docker container

Since we don't install postgres into the OS itself the operation is performed in a docker container. So the full command looks this way:

docker run --rm -v /home/app/_backups/{backup_id}:/backup postgres:15 sh -c "pg_dump --no-privileges --no-owner --no-acl <postgres_url> | gzip > /backup/<export_filename>"

Here's what each part of the command does:

docker run: This starts a new Docker container.

--rm: This tells Docker to remove the container automatically after it exits.

-v /home/app/_backups/{backup_id}:/backup: This mounts a directory on the host machine (/home/app/_backups/{backup_id}) as a volume inside the container (/backup). The backup_id is a variable that should be replaced with a specific ID value that corresponds to the backup directory.

postgres:15: This specifies the Docker image to use for the container. In this case, it's the official PostgreSQL 15 image.

sh -c: This starts a shell in the container and executes the command that follows.

"pg_dump --no-privileges --no-owner --no-acl <postgres_url> | gzip > /backup/<export_filename>": This is the command that's executed inside the container. It runs the pg_dump command with the specified options and pipes the output to the gzip command, which compresses it. The compressed output is then saved to a file in the /backup directory inside the container. <postgres_url> and <export_filename> are variables that should be replaced with the actual URL of the PostgreSQL server and the desired filename for the export file, respectively.

how to restore postgres database from backup file

In order to restore a postgres database from a file you need to download the backup file, uncompress it and import to the database.

For example we have a file called 23-database-starttest1-2023-04-18_21-51-01.gz. Then to uncomress it we use this command:

gunzip 23-database-starttest1-2023-04-18_21-51-01.gz

We will see 23-database-starttest1-2023-04-18_21-51-01 file.

Use psql command to load it to the database:

psql <new_postgresql_url> < 23-database-starttest1-2023-04-18_21-51-01