Сервер: Server 1 (159.194.225.30)
Версия: PostgreSQL 16.14
Статус: 🟢 running
Обновлено: 2026-07-04 (Денис)
| База | Владелец | Назначение |
|---|---|---|
poreklame_site |
wagtail | Wagtail CMS — production |
poreklame_dev |
wagtail | Wagtail CMS — development |
postgres |
postgres | Системная (default) |
template0 |
postgres | Шаблон |
template1 |
postgres | Шаблон |
wagtailw4gt4il_s3cur3_p4ss_2026localhost:5432openclaw (perms 600)# Беспарольный доступ (через .pgpass)
psql -U wagtail -h localhost -d poreklame_site
psql -U wagtail -h localhost -d poreklame_dev
# Сравнить список таблиц
diff <(psql -U wagtail -h localhost -d poreklame_site -t -c \
"SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname='public' ORDER BY tablename;") \
<(psql -U wagtail -h localhost -d poreklame_dev -t -c \
"SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname='public' ORDER BY tablename;")
# Сравнить menu items
diff <(psql -U wagtail -h localhost -d poreklame_site -t -c \
"SELECT id, label, link_url, sort_order FROM home_menuitem ORDER BY id;") \
<(psql -U wagtail -h localhost -d poreklame_dev -t -c \
"SELECT id, label, link_url, sort_order FROM home_menuitem ORDER BY id;")
# Сравнить count записей по всем таблицам
for t in $(psql -U wagtail -h localhost -d poreklame_site -t -c \
"SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname='public' ORDER BY tablename;"); do
t=$(echo $t | xargs)
c1=$(psql -U wagtail -h localhost -d poreklame_site -t -c "SELECT count(*) FROM \"$t\";" | xargs)
c2=$(psql -U wagtail -h localhost -d poreklame_dev -t -c "SELECT count(*) FROM \"$t\";" | xargs)
if [ "$c1" != "$c2" ]; then
echo "$t: prod=$c1 dev=$c2"
fi
done
# Не нужно: python manage.py dbshell
# Достаточно:
psql -U wagtail -h localhost -d poreklame_site
psql -U wagtail -h localhost -d poreklame_dev
# Подключиться как postgres
sudo -u postgres psql
# Список БД
sudo -u postgres psql -l
# Создать БД
sudo -u postgres psql -c "CREATE DATABASE newdb OWNER newuser;"
# Дамп prod
sudo -u postgres pg_dump poreklame_site | gzip > /tmp/backup.sql.gz
# Восстановление
zcat /tmp/backup.sql.gz | sudo -u postgres psql poreklame_site
# Клонирование prod → dev
sudo -u postgres pg_dump poreklame_site | sudo -u postgres psql poreklame_dev
local all postgres peer
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
Доступ только localhost. Удалённых подключений нет.
sudo systemctl status postgresql@16-main
sudo systemctl restart postgresql@16-main
Wagtail БД бекапится автоматически: backup-wagtail.sh (cron 03:30 MSK).
Для остальных БД — отдельный бэкап не настроен.