PostgreSQL backup database hosted on AWS EC2 without stopping or restarting the wizard

I am using PostgreSQL v9.1 for my organization. The database is hosted on Amazon Web Services (EC2 instance) under the Django web map, which performs tasks on the database (read / write data). The problem is to periodically back up this database in a specific format (see Requirements).

Requirements:

  • The backup server is available for backup.
  • Master dB should be backed up every hour. As soon as the hour is marked, db will be quickly copied completely and then copied to the sub file in the file system archive.
  • Along with the hourly backups, I need to make a daily daily backup of the database at midnight and a weekly backup at midnight of every Sunday.
  • Weekly backups will be final db backups. All weekly backups will be saved. Last week’s daily backups will only be saved, and hourly backups of the last day will be saved.

But I also have the following limitations.

  • Live data is sent to the server every day (insert speed in 2 seconds).
  • The database now stores important customer data, which means that it cannot be disabled.
  • , db , , master-db , ( )
  • / (, ), postgresql.conf pg_hba.conf, , , , . (. ).

master-db ? , ?

, master-db , master-db - .

+3
1

EBS

, pg_xlog, data, pg_clog .., EBS, EBS, , , ( ) . , EBS.

:

  • EBS API- EBS AWS , boto Python.

  • AWS API , , , .

  • EBS , , . PostgreSQL crashsafe, ( - , set fsync=off postgresql.conf). , , S3 - .

  • , , .

, , .

, , . , .

EBS?

LVM, LVM Linux. lvm-on-md-on-striped-ebs. lvm EBS , .

, .

LVM, EBS?

. pg_hba.conf, (pg_ctl reload SIGHUP postmaster), , .

, , , , .

, PITR, pg_basebackup, WAL-, pgbarman .. .

, . . (.. tcp/ip), :

  • PgBouncer
  • PgBouncer .
  • pgbouncer, postgresql.conf, . , . .
  • , .
  • SIGSTOP pgbouncer, .
  • , . , SIGCONT pgbouncer, .
  • PostgreSQL
  • , psql
  • SIGCONT pgbouncer

pgbouncer " ", , , , . , SIGSTOP ing pgbouncer ; , , .

WAL- PITR, pg_dump .

:

... , , , . "SQL Dump" "Continuous Archiving and Point-in-Time Recovery (PITR)".

PgBarman PITR , , WAL S3 . , WAL-E , S3. S3 .

(, S3, Glacier).

.

- , Amazon EC2, .

. , . , , . HA , , .

, , . -, , , , , , .

+4

All Articles