Рецепты по PostgreSQL

Текущий пользователь

SELECT SESSION_USER, CURRENT_USER

 

Список баз данных

 psql> \l

 

Список таблиц

 psql> \dt

 

Список пользователей

 psql> \du

 

Подключение к базе данных

 psql> \c name_database

 

Получение и установка значение последовательности

SELECT currval('user_user_id_seq'::regclass)
SELECT setval('user_user_id_seq'::regclass)

 

Смена пароля

To login without a password:

sudo -u user_name psql db_name

To reset the password if you have forgotten:

ALTER USER user_name WITH PASSWORD 'new_password';

 

Автоматический backup PostgreSQL

В файле:

sudo vi /etc/postgresql/12/main/pg_hba.conf

находим и меняем peer на trust

# Database administrative login by Unix domain socket
local   all             postgres                                peer (заменить на trust)

 далее перезапускаем службу:

 systemctl restart postgresql

Создаем файл с расширением .sh и вставляем в него код:

#!/bin/sh
DATA=`date +"%Y-%m-%d"`
database=db_name
backupfolder=~/backups
sqlfile=$backupfolder/$DATA-$database.sql
zipfile=$backupfolder/$DATA-$database.sql.gz

mkdir -p $backupfolder

if pg_dump -U db_user -d $database > $sqlfile ; then
   echo 'SQL dump created'
else
   echo 'pg_dump return non-zero code' #| mailx -s 'No backup was created!' $recipient_email
   exit
fi

if gzip -c -9 $sqlfile > $zipfile; then
   echo 'The backup was successfully compressed'
else
   echo 'Error compressing backup' #| mailx -s 'Backup was not created!' $recipient_email
   exit
fi
rm $sqlfile

#| gzip ~/backups/$DATA-$database.dump.gz
find $backupfolder -type f -mtime +60 -exec rm -rf {} \;

 Настраиваем cron

Запускаем cron

crontab -e

и добавляем задачу каждый день в 4 часа утра:

0 4 * * *  /home/user/backup_pg.sh

 

 Восстановление базы из backup'а

psql -U postgres db_name  /backups/2021-09-04-db_name.sql