INT 21h

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

Compare Postgresql tables by records count in tables

№ 11080 В разделе "Sysadmin" от July 16th, 2020,
В подшивках: ,

You have servers with master-slave(s) replication and want to be sure what all tables is synced. Program queries count of records for every table in database and shows to you on screen. Used on servers with logical replication and wal-g.

Create config.yaml with connection strings:

credentials:
  master: postgres://username:password@pg-master.server.ru:5433/mydatabase?sslmode=disable
  slave_logical: postgres://username:password@pg-slave1.server.ru:5432/mydatabase?sslmode=disable
  slave_walg: postgres://username:password@pg-slave2.server.ru:5435/mydatabase?sslmode=disable

Sample output, table ga is out of sync on slave_walg:

2020/07/16 05:45:45 master successfully connected!
2020/07/16 05:45:46 slave_logical successfully connected!
2020/07/16 05:45:46 slave_walg successfully connected!
2020/07/16 05:45:46 Table name: ga
2020/07/16 05:46:07 Postgres name: master Count: 25381158
2020/07/16 05:45:51 Postgres name: slave_logical Count: 25381158
2020/07/16 05:46:06 Postgres name: slave_walg Count: 25342939
2020/07/16 05:46:07 ------------

Source code and install https://git.blindage.org/21h/compare-postgres-tables

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

Логическая репликация в Postgresql

№ 11067 В разделе "Sysadmin" от July 10th, 2020,
В подшивках: , ,

Чтобы полностью понять принцип работы логической репликации нужно прочитать документацию. Если кратко, то логическая репликация передает не бинарные данные, а запросы INSERT, UPDATE, DELETE или их сочетание, что позволяет тонко управлять реплицируемыми данными, а также делать это между разными мажорными версиями СУБД. В моем случае я буду мигрировать с 10й версии на 12ю с маленьким даунтаймом. Вся концепция репликации строится на публикациях изменений и подписках, причем подписчик сам может публиковать данные уже для своих подписчиков.

На реплицируемой базе данных необходимо создать публикатор:

postgres=# \c zabbix 
You are now connected to database "zabbix" as user "postgres".
zabbix=# CREATE PUBLICATION zbx FOR ALL TABLES;
CREATE PUBLICATION

Не забываем в postgresql.conf поставить параметр wal_level = logical. Теперь можно сдампить текущую схему БД в файл, который позже будет залит в целевую БД.

postgres@boroda:/tmp$ pg_dump -h 127.0.0.1 -p 5432 -s zabbix > zabbix_schema.sql

Для уверенности можно проверить состояние публикатора

zabbix=# \dRp
                          List of publications
 Name |  Owner   | All tables | Inserts | Updates | Deletes | Truncates 
------+----------+------------+---------+---------+---------+-----------
 zbx  | postgres | t          | t       | t       | t       | t
(1 row)

А теперь на целевом сервере создадим новую базу, зальем схему и подпишемся на источник данных:

postgres=# create database zabbix;
CREATE DATABASE
postgres=# create role zabbix;
CREATE ROLE

А вот теперь можно и залить схему в новую БД:

postgres@boroda:/tmp$ cat /tmp/zabbix_schema.sql | psql -p 5434 zabbix

Ну вот и пришло время заливаться

postgres=# \c zabbix 
You are now connected to database "zabbix" as user "postgres".
zabbix=# CREATE SUBSCRIPTION zbx CONNECTION 'postgresql://postgres:postgres@127.0.0.1:5432/zabbix' PUBLICATION zbx WITH (slot_name=zbx_slot);
NOTICE:  created replication slot "zbx_slot" on publisher
CREATE SUBSCRIPTION

По умолчанию данные будут автоматически скопированы, но с помощью WITH можно изменить поведение подписки. Обратите внимание, что под репликацию лучше создать отдельного пользователя и ограничить ему доступ по IP в pg_hba.conf, но т.к. оба моих сервера находятся в безопасной среде мне на это пофигу и я копирую данные прямо суперюзером.

Проверим состояние подписки:

zabbix=# \dRs
          List of subscriptions
 Name |  Owner   | Enabled | Publication 
------+----------+---------+-------------
 zbx  | postgres | t       | {zbx}
(1 row)

Проверим начали ли данные передаваться:

zabbix=# select count(*) from hosts;
 count 
-------
    53
(1 row)

Все выглядит просто ништяк, данные перекачались в новое место, теперь можно переключать Zabbix со старой базы данных на новую, а потом просто дропнуть подписку.

Не забудьте перестроить индексы мигрировавшей базы данных:

zabbix=# reindex database CONCURRENTLY zabbix;

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

Сломался Postgresql

№ 11057 В разделе "Sysadmin" от June 29th, 2020,
В подшивках: ,

После жеского ребута сервера одним менеджером несмотря на многократное “никогда так не перезагружай сервер” сломалась БД.

sql> SELECT id, phone, url, pid, ip, ref, created, addtime FROM cc_phones WHERE idcampaign = '1891' AND pid not in ('6b6', 'fbf') AND addtime >= '2020-06-26 10:00' AND sent=False
[2020-06-29 16:41:44] [XX002] ERROR: index "cc_phones_addtime_idcampaign_index" contains unexpected zero page at block 266946
[2020-06-29 16:41:44] Подсказка: Please REINDEX it.

Жопа. Нужно сделать новые индексы и скорее всего совсем не одной таблицы, это долго.

farm=# reindex table cc_phones;
WARNING: concurrent insert in progress within table "cc_phones"
ERROR: could not access status of transaction 2885466886
DETAIL: Could not read from file "pg_subtrans/ABFC" at offset 188416: Success.
CONTEXT: while checking uniqueness of tuple (1217807,2) in relation "cc_phones"

Решилось созданием пустого файла pg_subtrans/ABFC из файла рядом, который также забит нулями.

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

wal-g for Postgresql replication

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

Initials

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

#!/bin/bash
source /etc/profile

export AWS_ENDPOINT=http://minio.myserv.com:9000
export AWS_ACCESS_KEY_ID="KEYNAMEKEYNAMEKEYNAME"
export AWS_SECRET_ACCESS_KEY="SECRETSECRETSECRETSECRETSECRETSECRET"
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.

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

Яндекс.Метрика

Fortune cookie: Spam del giorno: LA VOSTRA ATMOSFERA PRONTA PER L'ACCUMULAZIONE