Postgresql довідник по командам psql, pg_dump, pg_restore

PostgreSQL: довідник по командам psql, pg_dump, pg_restore

Всі команди запускаються під unix-користувачем postgres (postgresql-привілейований користувач). Для перемикання на користувача postgres використовується команда:







  • psql -l - список баз даних.
  • psql -d dbname - підключення до БД dbname.
  • psql -f file.sql - виконання команд з файлу file.sql.
  • psql -U postgres -d dbname -c "CREATE TABLE test (some_id serial PRIMARY KEY, some_text text);" - виконання команди в базі dbname.
  • psql -d dbname -H -c "SELECT * FROM test" -o test.html - висновок результату запиту в html-файл.

psql> SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

# Або список всіх конфігураційних параметрів

psql> show all;

Список активних сполук з інформацією про: pid процесу, що виконується запиту, користувача, бази даних.

psql> SELECT * FROM pg_stat_activity;

psql> ALTER TABLE tableName ADD PRIMARY KEY (id);

psql> CREATE UNIQUE INDEX indexName ON tableName (columnNames);

  • \ C dbname - під'єднання до БД dbname.
  • \ L - список баз даних.
  • \ Dt - список всіх таблиць.
  • \ D table - структура таблиці table.
  • \ Du - список всіх користувачів і їх привілеїв.
  • \ Dt + - список всіх таблиць з описом.
  • \ Dt * s * - список всіх таблиць, що містять s в імені.
  • \ I FILE - виконати команди з файлу FILE.
  • \ O FILE - зберегти результат запиту в файл FILE.
  • \ A - перемикання між відображенням: с / без вирівнювання.

Бекап і відновлення таблиць

У PostgreSQL є дві утиліти для бекапа pg_dump і pg_dumpall. pg_dump використовується для бекапу однієї бази, pg_dumpall для бекапу всіх баз і сервера в цілому (необхідно запускати під postgresql-суперкористувачем).

Створення бекапу бази mydb. в стислому вигляді:

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb

Створення бекапу бази mydb. у вигляді звичайного текстового файлу, включаючи команду для створення БД:







pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

Створення бекапу бази mydb. в стислому вигляді, з таблицями які містять в імені payments:

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t * payments * -f payment_tables.backup mydb

Дамп даних тільки однієї, конкретної таблиці. Якщо потрібно створити резервну копію декількох таблиць, то імена цих таблиць перераховуються за допомогою ключа -t для кожної таблиці.

pg_dump -a -t table_name -f file_name database_name

Створення резервної копії з стисненням в gz:

pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c> mydb.gz

Список найбільш часто використовуваних опцій:

  • -h host - хост, якщо не вказано то використовується localhost або значення з змінної оточення PGHOST.
  • -p port - порт, якщо не вказано то використовується 5432 або значення з змінної оточення PGPORT.
  • -u - користувач, якщо не вказано то використовується поточний користувач, також значення можна вказати у змінній оточення PGUSER.
  • -a, --data-only - дамп тільки даних, за замовчуванням зберігаються дані і схема.
  • -b - включати в дамп великі об'єкти (blog'і).
  • -s, --schema-only - дамп тільки схеми.
  • -C, --create - додає команду для створення БД.
  • -c - додає команди для видалення (drop) об'єктів (таблиць, видів і т.д.).
  • -O - не додавати команди для установки власника об'єкта (таблиць, видів і т.д.).
  • -F, --format - вихідний формат дампа, custom, tar, або plain text.
  • -t, --table = TABLE - вказуємо певну таблицю для дампа.
  • -v, --verbose - виведення докладної інформації.
  • -D, --attribute-inserts - дамп використовуючи команду INSERT з списком імен властивостей.

Бекап всіх баз даних використовуючи команду pg_dumpall:

pg_dumpall> all.sql

grep "^ [\] connect" all.sql

У PostgreSQL є дві утиліти для відновлення бази з бекапу.

  • psql - відновлення бекапів, які зберігаються в звичайному текстовому файлі (plain text);
  • pg_restore - відновлення стислих бекапів (tar);

Відновлення всього бекапу з ігноруванням помилок:

psql -h localhost -U someuser -d dbname -f mydb.sql

Відновлення всього бекапу з зупинкою на першій помилку:

psql -h localhost -U someuser --set ON_ERROR_STOP = on -f mydb.sql

Для відновлення з tar -аріхіва нам знадобитися спочатку створити базу за допомогою CREATE DATABASE mydb; (Якщо при створенні бекапу не було вказано опція -C) і відновити:

pg_restore --dbname = mydb --jobs = 4 --verbose mydb.backup

Відновлення резервної копії БД, стислій gz:

psql -U postgres -d mydb -f mydb

Починаючи з версії 9.2 можна відновити тільки структуру таблиць за допомогою опції --section:

CREATE DATABASE mydb2;

pg_restore --dbname = mydb2 --section = pre-data --jobs = 4 mydb.backup

VACUUM ANALYZE table;