Databases tips & tricks

Système

Postgresql - Dump

1
2
Dump all without Repmgr (nexus):
sudo -i -u postgres pg_dumpall --exclude-database=repmgr --data-only --column-inserts --inserts --on-conflict-do-nothing --role=nexus_user -f /pgsql_backup/nexus_data_transfert/db_nexus.dump

Postgresql - Retrait Tables

1
sudo sed -i -e '/public.batch_/d' -e '/public.download_count/d' /pgsql_backup/nexus_data_transfert/db_nexus.dump

Postgresql - Update Tables

1
2
sudo -u postgres /usr/pgsql-16/bin/psql -tAc "update repository set attributes = replace(attributes::TEXT,': 18444.0',': 8081.0')::jsonb where  name='docker-private';" nexus
sudo -u postgres /usr/pgsql-16/bin/psql -tAc "SELECT attributes from repository where name='docker-private';" nexus

Postgresql - Restore Dump with Errors Catch

1
sudo -i -u postgres psql -d nexus -f /bind_pg_backup/nexus_data_transfert/db_nexus.dump 2>log_errors.txt

Postgresql - Drop with Force

1
sudo -i -u postgres psql -tAc "DROP DATABASE IF EXISTS nexus WITH (FORCE);"

Postgresql - Reset Admin Password

1
2
3
4
sudo -u postgres /usr/pgsql-16/bin/psql nexus 
update security_user SET password='$shiro1$SHA-512$1024$NE+wqQq/TmjZMvfI7ENh/g==$V4yPw8T64UQ6GfJfxYq2hLsVrBY8D1v+bktfOxGdt4b/9BthpWPNUy/CBk6V9iA0nHpzYzJFWO8v/tZFtES8CA==', status='active' WHERE id='admin';
select * from security_user WHERE id='admin'; -> Verifier la présence de $shiro1
mdp = admin123

Import propre sql

1
mysql -u root -e "source /db.sql;"

Repmgr Cluster

En Vrac:

1
2
3
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf cluster show
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf standby switchover
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf node rejoin -d 'host=WQYD3762 user=repmgr dbname=repmgr connect_timeout=2' --force-rewind

Repmgr Simple Sync

1
2
3
sudo systemctl stop postgresql-16
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf node rejoin -d 'host={{ install_repmgr_standby_host }} user=repmgr dbname=repmgr connect_timeout=2' --force-rewind
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf standby switchover

Repmgr Hard Sync

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
sudo systemctl start postgresql-16
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf cluster show
sudo systemctl stop postgresql-16
sudo mv /var/lib/pgsql/16/data /var/lib/pgsql/16/data_$DATE_BACKUP
sudo mv /pgsql_wal/16/wals /pgsql_wal/16/wals_$DATE_BACKUP
echo "Sync data"
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf -h {{ install_repmgr_standby_host }} -U repmgr -d repmgr standby clone --force
sudo systemctl start postgresql-16
echo "Join Cluster as standby"
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf -h {{ install_repmgr_standby_host }} -U repmgr -d repmgr standby register --force
echo "Promoting to primary via switchover"
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf standby switchover
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf cluster show

echo "Echo if restoring success you can delete backups: /var/lib/pgsql/16/data_$DATE_BACKUP & /pgsql_wal/16/wals_$DATE_BACKUP"
postgresql repmgrd mysql