Как сделать копию таблицы в postgresql

SQL-Ex blog

В этой статье мы обсудим функциональность копирования базы данных PostgreSQL, которое является одним из наиболее важных и часто используемых на практике действий. Имеется два варианта оператора ‘COPY’, копирование базы данных и копирование таблицы. Мы обсудим оба эти варианта.

Сначала мы обсудим процесс копирования базы данных PostgreSQL на том же сервере или с одного сервера на другой, а затем поговорим о различных способах копирования существующей таблицы в новую таблицу на PostgreSQL.

Копирование базы данных в пределах одного и того же сервера

Иногда требуется создать точную копию существующей базы данных на том же сервере с целью разработки и тестирования. PostgreSQL помогает достичь этого с помощью оператора CREATE DATABASE, как показано ниже:

Базовый синтаксис:

CREATE DATABASE target_database 
WITH TEMPLATE source_database;

Этот оператор копирует source_database в target_database. Для объяснения сценария давайте рассмотрим приведенный ниже запрос.

CREATE DATABASE demo2 
WITH TEMPLATE demo;

Этот запрос дает указание ядру PostgreSQL создать новую базу данных с именем ‘demo2’, используя шаблон существующей базы данных ‘demo’. База данных копируется с сохранением внутренней структуры; это означает, что копируются все схемы и их таблицы и другие объекты, которые становятся доступными в новой базе данных.

Рисунки ниже дают подробное объяснение этого сценария.

До копирования базы данных ‘demo’. База данных ‘demo’ содержит пару схем, что показано выше на первом рисунке.

После выполнения запроса копирования создается база данных ‘demo2’ той же структуры.

Копирование базы данных с одного сервера на другой

Ниже объясняется наиболее эффективный способ копирования базы данных с одного сервера на другой. Процедура состоит из 4 шагов. Давайте рассмотрим эти шаги и соответствующие им команды.

1. Выполнение дампа исходной базы данных в файл SQL.

pg_dump -U postgres -d sourcedb -f sourcedb.sql

2. Копирование файла на целевой сервер посредством putty или любого другого процесса передачи файлов.

3. Создание новой базы данных на целевом сервере.

CREATE DATABASE targetdb;

4. Восстановление файла дампа на целевом сервере.

psql -U postgres -d targetdb -f sourcedb.sql

PostgreSQL — копирование таблицы

Функциональность копирования таблицы помогает скопировать существующую таблицу со всеми ее записями. Давайте рассмотрим базовый синтаксис всех возможных способов сделать это:

Копирование таблицы со всеми данными

CREATE TABLE new_table AS TABLE existing_table;

Пример

Рисунок показывает текущее состояние базы данных. Затем мы выполним следующий запрос копирования, чтобы создать новую таблицу из существующей таблицы books.

CREATE TABLE books2 AS TABLE books;

На этом рисунке мы можем увидеть новую таблицу с именем books2, которая имеет такую же структуру и тот же набор данных.

Копирование таблицы без данных

Также имеется возможность скопировать только структуру, но не данные. Этот сценарий особенно важен для тестирования приложения в разных средах.

CREATE TABLE new_table AS TABLE existing_table 
WITH NO DATA;

Давайте создадим новую таблицу с именем books3 из существующей таблицы books для лучшего понимания.

CREATE TABLE books3 AS TABLE books 
WITH NO DATA;

Тут важно отметить ответ ядра PostgreSQL после создания таблицы. В предыдущем сценарии ответом был текст SELECT 2′, говорящий о том, что в новую таблицу было вставлено 2 строки, а в этом примере ответ системы отличается — это текст ‘CREATE TABLE AS’, что означает создание таблицы без данных.

Копирование таблицы с частью данных
В этом последнем сценарии мы увидим, как можно создать новую таблицу из существующей с частью данных. Здесь внутренний запрос используется совместно с предложением WHERE для получения желаемого результата.

CREATE TABLE new_table AS 
SELECT * FROM existing_table WHERE condition;

Пример

CREATE TABLE books4 AS 
SELECT * FROM books WHERE /> 

Как объяснялось выше, мы видим, что была создана таблица только с одной строкой данных на основе входных данных в предложении WHERE.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Резервное копирование и восстановление PostgreSQL

В этой инструкции описывается, как сделать дамп базы данных PostgreSQL в консоли при подключении по SSH с помощью утилит pg_dump, pg_dumpall, pg_restore.

На этой странице

• Перед началом работы
• Создание бэкапа одной БД с помощью pg_dump
• Создание бэкапа всех имеющихся БД с помощью pg_dumpall
• Восстановление из бэкапа с помощью pg_restore

Перед началом работы

1. Войдите в консоль управления Advanced.

• Инструкция по входу с помощью личного кабинета Cloud.ru
• Инструкция по входу в консоль для IAM-пользователей

Создание бэкапа одной БД с помощью pg_dump

Утилита pg_dump — встроенный инструмент для создания резервных копий в PostgreSQL. Утилита имеет синтаксис:

pg_dump параметры> название БД> > файл для сохранения копии> 

1. Подключитесь к виртуальной машине и обновите на ней репозитории.

apt-get update 

apt-get install postgresql-client 

pg_dump -U user> -W -h host> -p port> datastore> > /tmp/datastore-backup>.dump 

В этом примере используется БД PostgreSQL с внешним IP (EIP) и названием БД «postgres», для входа необходимо указать пользователя root, а также пароль.

pg_dump -U root -W -h 172.16.0.49 postgres > /tmp/postgres.dump 

• -U — имя пользователя экземпляра базы данных RDS. По умолчанию — root .
• -h — IP-адрес экземпляра первичной БД. Получить этот IP-адрес можно на странице Instance Management , нажав на название экземпляра БД.

Примечание Если подключение осуществляется:

• через ECS, то IP-адрес можно найти на вкладке Basic Information в разделе Connection Information → Floating IP Address .
• через EIP, то адрес IP будет располагаться на вкладке EIPs .

В результате будет выполнен бэкап БД в файл «postgres» с расширением .dump.

Создание бэкапа всех имеющихся БД с помощью pg_dumpall

Утилита pg_dumpall реализует резервное копирование всего экземпляра (кластера или инстанса) базы данных без указания конкретной базы данных на инстансе. Принцип работы с ней аналогичен pg_dump.

Установите клиент на виртуальную машину.

apt-get install postgresql-client 

pg_dumpall -U user> -W -h host> -p port> datastore> > /tmp/datastore-backup>.bak 

Для сжатия резервной копии рекомендуется сразу передать вывод на архиватор gzip:

pg_dumpall datastore> | gzip > /tmp/datastore-backup>.tar.gz 

В этом примере используется БД PostgreSQL с внешним IP (EIP) и названием БД «postgres», для входа необходимо указать пользователя root, а также пароль.

pg_dumpall -U root -W -h 172.16.0.49 postgres > /tmp/postgres.bak 

Параметры утилиты аналогичны pg_dump.

В результате будет выполнен бэкап всех имеющихся в системе БД в файл «postgres» с расширением .bak.

Восстановление из бэкапа с помощью pg_restore

Утилита позволяет восстанавливать данные из резервных копий. Например, чтобы восстановить только определенную БД (в нашем примере postgres), нужно запустить эту утилиту с параметром -d :

pg_restore -U user> -W -h host> -p port> -d datastore> /tmp/datastore-backup>.bak 

В этом примере используется БД PostgreSQL с внешним IP (EIP) и названием БД «postgres», для входа необходимо указать пользователя root, а также пароль.

pg_restore -U root -W -h 172.16.0.49 -d postgres /tmp/postgres.bak 

• -U — имя пользователя экземпляра базы данных RDS. По умолчанию — root .
• -h — IP-адрес экземпляра первичной БД. Получить этот IP-адрес можно на странице Instance Management , нажав на название экземпляра БД.

Примечание Если подключение осуществляется:

• через ECS, то IP-адрес можно найти на вкладке Basic Information в разделе Connection Information → Floating IP Address .
• через EIP, то адрес IP будет располагаться на вкладке EIPs .

• p, plain — формирует текстовый SQL-скрипт;
• c, custom — формирует резервную копию в архивном формате;
• d, directory — формирует копию в directory-формате;
• t, tar — формирует копию в формате tar.

Как сделать копию таблицы в postgresql

CREATE TABLE AS — создать таблицу из результатов запроса

Синтаксис

CREATE [ [ GLOBAL | LOCAL ] < TEMPORARY | TEMP >| UNLOGGED ] TABLE [ IF NOT EXISTS ] имя_таблицы [ (имя_столбца [, . ] ) ] [ WITH ( параметр_хранения [= значение] [, . ] ) | WITH OIDS | WITHOUT OIDS ] [ ON COMMIT < PRESERVE ROWS | DELETE ROWS | DROP >] [ TABLESPACE табл_пространство ] AS запрос [ WITH [ NO ] DATA ]

Описание

CREATE TABLE AS создаёт таблицу и наполняет её данными, полученными в результате выполнения SELECT . Столбцы этой таблицы получают имена и типы данных в соответствии со столбцами результата SELECT (хотя имена столбцов можно переопределить, добавив явно список новых имён столбцов).

CREATE TABLE AS напоминает создание представления, но на самом деле есть значительная разница: эта команда создаёт новую таблицу и выполняет запрос только раз, чтобы наполнить таблицу начальными данными. Последующие изменения в исходных таблицах запроса в новой таблице отражаться не будут. С представлением, напротив, определяющая его команда SELECT выполняется при каждой выборке из него.

Параметры

GLOBAL или LOCAL

Для совместимости игнорируются. Использование этих ключевых слов считается устаревшим; за подробностями обратитесь к CREATE TABLE .

TEMPORARY или TEMP

Если указано, создаваемая таблица будет временной. За подробностями обратитесь к CREATE TABLE . UNLOGGED

Если указано, создаваемая таблица будет нежурналируемой. За подробностями обратитесь к CREATE TABLE . IF NOT EXISTS

Не считать ошибкой, если отношение с таким именем уже существует. В этом случае будет выдано замечание. За подробностями обратитесь к описанию CREATE TABLE . имя_таблицы

Имя создаваемой таблицы (возможно, дополненное схемой). имя_столбца

Имя столбца в создаваемой таблице. Если имена столбцов не заданы явно, они определяются по именам столбцов результата запроса. WITH ( параметр_хранения [= значение ] [, . ] )

Это предложение определяет дополнительные параметры хранения для новой таблицы: за подробностями обратитесь к Параметры хранения. Предложение WITH может также включать указание OIDS=TRUE (или просто OIDS ), с которым строкам в новой таблице будут назначаться идентификаторы объектов (OID), либо указание OIDS=FALSE , с которым строки не будут содержать OID. За дополнительными сведениями обратитесь к CREATE TABLE . WITH OIDS
WITHOUT OIDS

Это устаревшее написание указаний WITH (OIDS) и WITH (OIDS=FALSE) , соответственно. Если требуется определить одновременно свойство OIDS и параметры хранения, необходимо использовать синтаксис WITH ( . ) ; см. ниже. ON COMMIT

Поведением временных таблиц в конце блока транзакции позволяет управлять предложение ON COMMIT , которое принимает три параметра:

PRESERVE ROWS

Никакое специальное действие в конце транзакции не выполняется. Это поведение по умолчанию. DELETE ROWS

Все строки в этой временной таблице будут удаляться в конце каждого блока транзакции. По сути, при каждой фиксации транзакции будет автоматически выполняться TRUNCATE . DROP

Эта временная таблица будет удаляться в конце текущего блока транзакции.

TABLESPACE табл_пространство

Здесь табл_пространство — имя табличного пространства, в котором будет создаваться новая таблица. Если оно не указано, выбирается default_tablespace или temp_tablespaces, если таблица временная. запрос

Команда SELECT , TABLE или VALUES , либо команда EXECUTE , выполняющая подготовленный запрос SELECT , TABLE или VALUES . WITH [ NO ] DATA

Это предложение определяет, будут ли данные, выданные запросом, копироваться в новую таблицу. Если нет, то копируется только структура. По умолчанию данные копируются.

Замечания

Функциональность этой команды подобна SELECT INTO , но предпочтительнее использовать её, во избежание путаницы с другими применениями синтаксиса SELECT INTO . Кроме того, набор возможностей CREATE TABLE AS шире, чем у SELECT INTO .

Команда CREATE TABLE AS позволяет пользователю явно определить, добавлять ли OID в таблицу. Если присутствие OID не определено явно, оно определяется конфигурационной переменной default_with_oids.

Примеры

Создание таблицы films_recent , содержащей только последние записи из таблицы films :

CREATE TABLE films_recent AS SELECT * FROM films WHERE date_prod >= '2002-01-01';

Чтобы скопировать таблицу полностью, можно также использовать короткую форму команды TABLE :

CREATE TABLE films2 AS TABLE films;

Создание временной таблицы films_recent , содержащей только последние записи таблицы films , с применением подготовленного оператора. Новая таблица будет содержать OID и прекратит существование при фиксации транзакции:

PREPARE recentfilms(date) AS SELECT * FROM films WHERE date_prod > $1; CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS EXECUTE recentfilms('2002-01-01');

Совместимость

CREATE TABLE AS соответствует стандарту SQL . Нестандартные расширения перечислены ниже:

Стандарт требует заключать предложение подзапроса в скобки, но в PostgreSQL эти скобки необязательны.

Стандарт требует наличия указания WITH [ NO ] DATA , в PostgreSQL оно необязательно.

PostgreSQL работает с временными таблицами не так, как описано в стандарте; за подробностями обратитесь к CREATE TABLE .

Предложение WITH является расширением PostgreSQL ; в стандарте ни параметры хранения, ни OID не оговариваются.

Резервное копирование и восстановление баз данных PostgreSQL

В статье мы расскажем об инструментах PostgreSQL, которые позволяют сохранить дамп БД и развернуть его.

Что такое PostgreSQL

PostgreSQL — это объектно-реляционная система управления базами данных. Она относится к категории свободно распространяемых и имеет открытый исходный код.

Какие преимущества имеет PostgreSQL относительно других СУБД:

• возможность работать в реляционном и объектном подходе;
• поддержка разных форматов данных: например XML, JSON или NoSQL;
• нет ограничений на объем базы данных и количество записей в ней;
• возможность писать функции на разных языках программирования;
• поддержка составных запросов;
• доступ к базе с нескольких устройств одновременно и другие.

В некоторых случаях могут понадобиться бэкапы баз данных: например, вы планируете перенести информацию на другой сервер или просто хотите обезопасить проект и скопировать важные данные. В этом помогут встроенные мини-программы PostgreSQL, которые называются утилитами.

Как сделать дамп базы данных

Чтобы выполнить резервное копирование данных в PostgreSQL можно использовать разные утилиты:

• pg_dump,
• pg_dumpall,
• pg_basebackup.

Подробнее о каждой из них мы расскажем ниже.

pg_dump

С помощью утилиты pg_dump вы можете создать целостный бэкап одной базы данных PostgreSQL, причем в процессе копирования можно продолжать работу с БД. В готовом дампе будет храниться только база данных — глобальные объекты (роли или табличные пространства) нужно сохранять с помощью других программ.

Чтобы создать бэкап (pg_dump):