SQL-Ex blog

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

Иногда требуется создать точную копию существующей базы данных на том же сервере с целью разработки и тестирования. 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 id=19;

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