How to Import and Export Databases Using phpPgAdmin

To manage the PostgreSQL databases in your hosting account you should use additionally installed phpPgAdmin.

Exporting Data into .sql File

With phpPgAdmin you can export the entire database or a single schema.

Depending on what you want to export, check the database name or the schema box and click Export.

To extract all data together with the database/schema structure, you should first select Structure and data (1) and then SQL (2) for Format.

Select Download (3) and finish by clicking Export (4). You can choose where to save the generated .sql file locally on your PC.

If you select the Show option instead of downloading the .sql file, you will see its content displayed in a tab. The “Download compressed with gzip” option will also generate .sql file, but it will compress the file (dump.sql.gz).

Importing Data from .sql File

To import data with phpPgAdmin click on the database name in the list on the left.

Select SQL and then Browse from the left panel showing a list of all schemas.

After selecting the .sql file for import, finish by clicking on Execute.

Important: To import data into the PostgreSQL database you need to use additionally installed phpPgAdmin in your hosting account. You also need to log in as the user assigned to this database.

Important: To successfully import data into PostgreSQL database make sure that:

1. The schema exists.
   If the schema where the import will be executed already exists, you should not request creating it in the .sql file. Here is an example for schema generating code that can be removed from the .sql file:
   CREATE SCHEMA wolf;
   ALTER SCHEMA wolf OWNER TO cpuser_mypgusr;
   COMMENT ON SCHEMA wolf IS ‘-‘;
2. The .sql file contains the database user which you actually use to import the data. Wherever there is a username it should be your username. For example “Owner: cpuser_mypgusr“
3. The .sql script contains the appropriate schema. Just like the username, here the name of the schema for the import should be written everywhere. In the following line:
   SET search_path = wolf, pg_catalog;
   the first schema should be the one where the import will be executed.
   All Schema scripts such as: wolf; should contain the appropriate schema name.

Possible Failure Messages While Importing

ERROR: schema “wolf” already exists

The wolf schema already exists, but the .sql file does contain information for its generation. In such case the import will be successful despite this message (step 1).

ERROR: role “cpuser_pguser” does not exist

This means that you should change the user in the .sql file to the user you are now logged in with to import data (step 2).

ERROR: must be owner of extension plpgsql

The plpgsql extension is installed on the server and you are trying to modify its comment. This extension enables you to use the plpgsql procedural/programming language. The language is used to create functions, embedded procedures and etc. in the database.
Check the .sql file again and remove such lines, if you discover them:

Проверете отново .sql файла и ако откриете следните редове – премахнете ги:
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS ‘PL/pgSQL procedural language’;

ERROR: relation “users” already exists
ERROR: function “date_format” already exists with same argument types
ERROR: relation “wolf_cron” already exists

Failure messages, containing the “already exists” phrase mean that a certain element is already present in the database. Before importing, make sure that the database/schema is clean, meaning that it does not contain already existing schemas, tables, constraints; sequences, functions, etc. with the same names.