Skip to content

Manual backup of database

Backup database using pg_dump utility

A backup of the MiaRec database can be executed on demand using the pg_dump utility. This is a good idea to execute on initial deployment, to verify operation. The pg_dump utility is installed as part of the postgresql package, so it does not need to be installed.

Execute the pg_dump utility:

sudo -iu postgres pg_dump -F c -f /tmp/miarecdb-$(date "+%Y.%m.%d-%H.%M.%S").backup miarecdb

Let's break down those options:

  • sudo -iu postgres instructs the shell to execute the trailing command on behalf of user postgres, this is required for authentication to the database.

  • pg_dump calls the pg_dump utility.

  • -F c Sets an output file format, in this case, a custom archive suitable for input into pg_restore. This is the most flexible format in that it allows reordering of loading data as well as object definitions. This format is also compressed by default.

  • -f /path/to/outputfile Send output to the specified path, this directory will need to be accessed by the postgres user, a directory like /tmp would be a suitable destination.

  • $(date "+%Y.%m.%d-%H.%M.%S") will be replaced with the current timestamp, like 2022.08.02-12.13.14.

  • miarecdb the target database for dump, this should always be miarecdb.

Verification

An archive will be produced at the specified path 

[centos@miarecdb ~]$ ls -l /tmp
-rw-rw-r--. 1 postgres postgres 1102336 Aug  2 16:35 miarecdb-2022.08.02-12.13.14.backup
[centos@miarecdb ~]$

Copy database backup files to S3

Set AWS credentials

Use the previously created user credentials for the the dabase backup bucket. Using environmental variables, these values will be set for this session, but not be retained after the session is ended.

export AWS_ACCESS_KEY_ID=<AWS_ACCESS_KEY_ID>
export AWS_SECRET_ACCESS_KEY=<AWS_AC_KEY_ID>

Execute Copy to S3

aws s3 cp /path/to/source s3://{s3-bucket-name}

Let's break down those options:

  • aws s3 cp calls the aws cli utility and instructs it to copy a file to s3.

  • /path/to/source path and file name of source database dump file.

  • s3://{s3-bucket-name} Sets a destination of s3 transfer, this should be the bucket created in an earlier step.

Example. Copy a file from the /tmp directory to S3 bucket miarec-db-backup:

aws s3 cp /tmp/miarecdb-2022.08.17-21.05.24.backup s3://miarec-db-backup

Result

The object will be moved to the defined bucket

[centos@miarecdb ~]$ aws s3 cp /tmp/miarecdb-2022.08.25-19.47.14.backup s3://miarec-db-backup
upload: ../../tmp/miarecdb-2022.08.25-19.47.14.backup to s3://miarec-db-backup/miarecdb-2022.08.25-19.47.14.backup
[centos@miarecdb ~]$

Object Moved To Defined Bucket