Импорт базы данных postgresql linux

How to Import and Export PostgreSQL Database

PostgreSQL is the modern and most popular database management system now a day. Backing up PostgreSQL data is one of the most important tasks for any database administrator. It is an essential component of a data storage plan. Backups will help you to recover your system in the event of database corruption. PostgreSQL provides pg_dump and a pg_dumpall command-line utility to effectively backup your important information.

When working on a different project in different environments, you often need to export a database dump from one server and import it into another server. So it is essential to implement a backup strategy to protect against data loss.

In this post, we will show you how to import and export a PostgreSQL database using pg_dump, pg_dumpall, psql, and pg_restore utility.

Export a PostgreSQL Database with pg_dump Command

The pg_dump is a command-line tool that allows you to dump the content of a specified database into a single file. The pg_dump command provides several options that help you to specify tables or schemas to back up.

The basic syntax of the pg_dump command is shown below:

• -h Specify database server host
• -p Specify database server port
• -U Specify the user which is used to connect to the PostgreSQL database server
• -W Used to prompt for a password before connecting to the PostgreSQL server
• -d Specify the database to dump
• -F Specify one of the following output format files:
• p plain-text format
• c custom format
• d directory format
• t tar format

To export a database named testdb into a plain-text format, run the following command:

 
pg_dump -U username -W -F p -d testdb > testdb.sql

To export a database named testdb into a tar format, run the following command:

 
pg_dump -U username -W -F t -d testdb > testdb.tar

To export a database named testdb into custom format, run the following command:

 
pg_dump -U username -W -F c -d testdb > testdb.dump

To export a database named testdb into a directory format, run the following command:

 
pg_dump -U username -W -F d -d testdb -f testdb-dir

If you want to export a large database and generate a smaller dump file, you can use the gzip tool with the pg_dump command to compress the dump file.

 
pg_dump -U username -W -F t -d testdb | gzip > testdb.tar.gz

Export All PostgreSQL Databases with pg_dumpall Command

The pg_dumpall is a command-line utility that allows you to export all PostgreSQL databases into a single file. This file contains several SQL commands that can be used as input to psql to restore the databases. The pg_dumpall reads tables from all databases, so you will have to connect to PostgreSQL as a superuser to produce a complete dump.

The basic syntax of the pgdump_all command is shown below:

 
pg_dumpall [option] > dump.sql

A brief explanation of each option is shown below:

• -h Specify database server host
• -p Specify database server port
• -c Clean databases before recreating
• -g Dump only global objects, no databases
• -r Dump only roles, no databases or tablespaces -s Dump only the schema, no data -S Specify superuser user name to use in the dump -t Dump only tablespaces definition.

To export all PostgreSQL databases, run the following command:

To back up objects in all databases, including roles, tablespaces, databases, schemas, tables, indexes, triggers, functions, constraints, views, ownerships, and privileges, you use the following command:

If you want to back up role definition only, use the following command:

If you want to back up the tablespaces definition, use the following command:

Import a PostgreSQL Database with pg_restore Command

The pg_restore is a command-line utility used to import or restore a PostgreSQL database from an archive created by pg_dump. Generally, it is used if you choose custom, directory, or tar format when creating a dump file.

The pg_restore command provides a -j option to specify the number of threads for restoration. This will allow you to perform parallel restores and dramatically speed up the process. Currently, the pg_restore support this option for the only custom file format.

The basic syntax of the pg_restore command is shown below:

 
pg_restore -c -U db_user -W -F t -d db_name dump_file.tar

A brief explanation of each option is shown below:

• -U Specify the user who is used for connecting to the PostgreSQL database server.
• -d Specify the database on which you want to import or restore a database.
• -W Used to prompt for a password before connecting to the PostgreSQL server.
• -F format Specify the format of the archive.
• -j Specify the number of parallel jobs to restore.
• -c Clean a database before restoring them.
• -C Create a new database before restoring it.
• -e Exit the process if an error has been encountered.

For example, to import a single database named testdb from the dump file named dump.tar, run the following command:

 
pg_restore -c -U username -W -F t -d testdb dump.tar

If you want to import all databases from the alldump.tar file, run the following command:

 
pg_restore -c -U username -W -F t alldump.tar

Import a PostgreSQL Database with psql Command

The psql is a command-line tool that allows you to import the SQL dump file generated by the pg_dump or pg_dumpall.

The basic syntax to import a single database using the psql tool is shown below:

 
psql -U user_name -W -d db_name -f dump.sql

The basic syntax to import all databases using the psql tool is shown below:

 
psql -U user_name -W -f dump.sql

To import a single database from the dump file named dump.sql into the database named testdb, run the following command:

 
psql -U username -W -d testdb -f dump.sql

If you want to stop importing a database in case of errors, you add the —set ON_ERROR_STOP=on option:

 
psql -U username -W -d testdb --set ON_ERROR_STOP=on -f dump.sql

To import all PostgreSQL databases from the dump file named alldb_dump.sql, run the following command:

 
psql -U username -W -f alldb_dump.sql

Conclusion

In this guide, we explained how to export a PostgreSQL database using the pg_dump and pg_dumpall utility. We also explained how to import a dump file using the pg_restore and psql utility. I hope this will help you to perform your day-to-day backup jobs.

Thank you for helping us improve!

Источник

Импорт и экспорт данных в PostgreSQL, гайд для начинающих

В процессе обучения аналитике данных у человека неизбежно возникает вопрос о миграции данных из одной среды в другую. Поскольку одним из необходимых навыков для аналитика данных является знание SQL, а одной из наиболее популярных СУБД является PostgreSQL, предлагаю рассмотреть импорт и экспорт данных на примере этой СУБД.

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

В данной статье приведены примеры импорта в PostgreSQL непосредственно самой базы данных в формате sql, а также импорта и экспорта данных в наиболее простом и распространенном формате .csv, в котором в настоящее время хранятся множество существующих датасетов. Формат .json хоть и является также очень распространенным, рассмотрен не будет, поскольку, по моему скромному мнению, с ним все-таки лучше работать на Python, чем в SQL.

1. Импорт базы данных в формате в PostgreSQL

Скачиваем (получаем из внутреннего корпоративного источника) файл с базой данных в выбранную папку. В данном случае путь:

Имя файла: demo-big-20170815

Далее понадобиться командная строка windows или SQL shell (psql). Для примера воспользуемся cmd. Переходим в каталог, где находится скачанная БД, командой cd C:\Users\User-N\Desktop\БД :

Далее выполняем команду для загрузки БД из sql-файла:

«C:\Program Files\PostgreSQL\10\bin\psql» -U postgres -f demo-big-20170815.sql

Где сначала указывается путь, по которому установлен PostgreSQL на компьютере, -U – имя пользователя, -f — название файла БД.

Отметим, что в зависимости от размера базы данных загрузка может занимать до нескольких десятков минут. Конец загрузки будет отмечен следующим видом:

Заходим в pgAdmin и наблюдаем там импортированную БД:

2. Импорт данных из csv-файла

Предполагается, что у вас уже есть необходимый .csv-файл, и первое, что нужно сделать, это перейти pgAdmin и создать там новую базу данных. Ну или воспользоваться уже существующей, в зависимости от текущих нужд. В данном случае была создана БД airtickets.

В выбранной БД создается таблица с полями, типы которых должны соответствовать «колонкам» в выбранном .csv-файле.

Далее воспользуемся SQL shell (psql) для подключения к нужной БД и для подачи команд на импорт данных. При открытии SQL shell (psql) она стандартно спросит про имя сервера, имя подключаемой БД, порт и пользователя. Ввести нужно только имя БД и пароль пользователя, всё остальное проходим нажатием ентра. Создается подключение к нужной БД – airtickets.

Ну и вводим команды на импорт данных из файла:

\COPY tickets FROM ‘C:\Users\User-N\Desktop\CSV\ticket_dataset_MOW.csv’ DELIMITER ‘,’ CSV HEADER;

Где tickets – название созданной в БД таблицы, из – путь, где хранится .csv-файл, DELIMITER ‘,’ – разделитель, используемый в импортируемом .csv-файле, сам формат файла и HEADER , указывающий на заголовки «колонок».

Один интересный момент. Написание команды COPY строчными (маленькими) буквами привело к тому, что psql ругнулся, выдал ошибку и предложил написать команду прописными буквами.

Заходим в pgAdmin и удостоверяемся, что данные были загружены.

3. Экспорт данных в .csv-файл

Предположим, нам надо сохранить таблицу airports_data из уже упоминаемой выше БД demo.

Для этого подключимся к БД demo через SQL shell (psql) и наберем команду, указав уже знакомые параметры разделителя, типа файла и заголовка:

\COPY airports_data TO ‘C:\Users\User-N\Desktop\CSV\airports.csv’ DELIMITER ‘,’ CSV HEADER;

Существует и другой способ экспорта через pgAdmin: правой кнопкой мыши по нужной таблице – экспорт – указание параметров экспорта в открывшемся окне.

4. Экспорт данных выборки в .csv-файл

Иногда возникает необходимость сохранить в .csv-файл не полностью всю таблицу, а лишь некоторые данные, соответствующие некоторому условию. Например, нам нужно из БД demo таблицы flights выбрать поля flight_id, flight_no, departure_airport, arrival_airport, где departure_airport = ‘SVO’. Данный запрос можно вставить сразу в команду psql:

\COPY (SELECT flight_id, flight_no, departure_airport, arrival_airport FROM flights WHERE departure_airport = ‘SVO’) TO ‘C:\Users\User-N\Desktop\CSV\flights_SVO.csv’ CSV HEADER DELIMITER ‘,’;

Вот такой небольшой гайд получился.

Источник