INT 21h

Hi, I am Vladimir Smagin, SysAdmin and Kaptain. Telegram Email / GIT / Thingiverse / RSS / GPG

How to move snap application between computers with snapshots

№ 10960. В разделе " Sysadmin " от March 29th, 2020

В подшивках: ,

wal-g for Postgresql replication

№ 10938 В разделе "Sysadmin" от March 20th, 2020,
В подшивках: , ,


Master database: postgresql-10, in production
Backup storage: minio with empty pg-walg-backup bucket

Task: Create slave postgresql-10 server with recovery

Preparing for both servers

Install wal-g binary manually or use my Ubuntu repository (install walg-lzo).

Create special script for wal-g with variables, save to /var/lib/postgresql/walg-postgresql

source /etc/profile

export WALE_S3_PREFIX="s3://pg-walg-backup/"
export AWS_S3_FORCE_PATH_STYLE="true"
export AWS_REGION=us-east-1
export PGHOST=/var/run/postgresql
export PGUSER=postgres
export PGPORT=5433

wal-g $@

It is not secure to store this file global readable. Best choice to place it in postgres home directory without read by others:

chmod 500 /var/lib/postgresql/walg-postgresql
chown postgres:postgres /var/lib/postgresql/walg-postgresql

Master server

Add to postgresql.conf:

archive_mode = on
archive_command = '/var/lib/postgresql/walg-postgresql wal-push %p'

Ok, now run backup-push to create full backup:

/var/lib/postgresql/walg-postgresql backup-push /pg_data/10/main

Slave server

Stop postgresql and remove data directory, wal-g requires empty directory before restore.

Now run:

/var/lib/postgresql/walg-postgresql backup-fetch /pgdata/10/main LATEST

Create /pgdata/10/main/recovery.conf:

standby_mode = 'on'
restore_command = 'walg-postgresql wal-fetch "%f" "%p"'

Now see logfile for wal restoring, database in recovery mode.

Postgresql 12: recovery settings moved to postgresql.conf. To start infinite recovery create flag file /var/lib/postgresql/12/main/standby.signal.

Нет комментариев »

Postgresql logical backup failed, pg_dump said: PQgetResult() failed

№ 10947 В разделе "Sysadmin" от March 20th, 2020,
В подшивках: , ,

You made streaming backups and restoring with walg and now trying to make logical backups.

But some shit ruined your plans:

pg_dump: Dumping the contents of table "<table>" failed: PQgetResult() failed.

Many sites suggests to enable hot_standby_feedback in postgresql.conf, but actually you need also increase max_standby_archive_delay and max_standby_streaming_delay. I set 1 hour.

Нет комментариев »

Backup MySQL в Docker

№ 9567 В разделе "Sysadmin" от November 22nd, 2018,
В подшивках: , ,


docker exec CONTAINER /usr/bin/mysqldump -u root --password=root DATABASE > backup.sql


cat backup.sql | docker exec -i CONTAINER /usr/bin/mysql -u root --password=root DATABASE

Нет комментариев »

Postgres backup to FTP from Docker or directly, see examples

№ 9473 В разделе "Sysadmin" от October 23rd, 2018,
В подшивках: , ,

You can easily dump your Postgresql database from Docker container or directly and you have 2 ways to do it. First variant more dangerous, depends on internet connection, but not requires a lot of disk space. Second variant more stable, but if free space suddenly is out your backup will be failed. You can add error checking and reporting to Zabbix like here.

Variant 1: stream backing up file to FTP (directly)

source /etc/profile
weekday=$(date '+%w')
pg_dump postgresql:// | gzip | curl -u ftplogin:ftppassword \$weekday/db_backup.sql.gz --ftp-create-dirs -T -

Variant 2: dump to local host, upload and delete local file (from Docker)

source /etc/profile



weekday=$(date '+%w') # 7 days history

docker exec -it pg-slave-server su -c "pg_dump postgresql://${db_user}:${db_pass}@${db_host}:${db_port}/${db_name}" \
    postgres |gzip -7 > /tmp/db_backup_${db_name}_${history}.sql.gz
curl --upload-file /tmp/db_backup_${db_name}_${history}.sql.gz ftp://${ftp_user}:${ftp_pass}@${ftp_serv}/
rm -f /tmp/db_backup_${db_name}_${history}.sql.gz

Нет комментариев »


Fortune cookie: Today's spam: Nobody can be blamed for your pitiful member but you can change it.