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;