Export Import of PostgreSQL data

Export Import of PostgreSQL data

To export data from a PostgreSQL database:

$ pg_dump-u postgres-f mydatabase.sql mydatabase

When importing data, first create the database and then import it with Psql:

$ createdb Newdatabase
$ psql-d newdatabase-u postgres-f Mydatabase.sql

Create a database

CREATE DATABASE Test with OWNER = postgres ENCODING = ' UTF8 ';

Enter the console method to execute the command under the installation purpose bin of PostgreSQL: Psql database name,
Example:/usr/local/pgsql/bin/psql mydb

Specify host, user name, and database, such as:

/usr/local/pgsql/bin/psql-h localhost-u postgres-d Test

View version: Psql--version or SELECT version ();

View all databases: \l

View all databases (including detailed parameters): SELECT * from Pg_database;

Select database: \c databasename

View All tables: \dt

To view the structure of a table: \d tablename

Exit Psql Console: \q

To view the index of a table:

SELECT * from pg_indexes where tablename= ' log ';

To export a backup database:

Pg_dump-h localhost-u postgres databasename >/tmp/databasename.bak.yyyymmdd.sql

Import the Recovery database (the SQL file is the Pg_dump exported file, either the entire database, or just a single table, or just a structure, etc.):

Psql-h localhost-u postgres-d DatabaseName </tmp/databasename.bak.yyyymmdd.sql

Export data structure, mainly with the parameter-s:

Pg_dump-u username-w dbname-f/tmp/filename.sql

To export a table:
Pg_dump-h localhost-u postgres-t tablename dbname > Test.sql

Export the structure of a table, also add parameter "-S":
Pg_dump-h localhost-u postgres-t tablename-s dbname > Test_construct.sql

To export data for a table, add the parameter "-a":
Pg_dump-h localhost-u postgres-t tablename-a dbname > Test_data.sql

View sequence: SELECT * from information_schema.sequences where Sequence_schema = ' public ';

View database size: Select Pg_size_pretty (pg_database_size (' Test '));

View the size of the table: Select Pg_size_pretty (pg_relation_size (' Test '));

Export Import of PostgreSQL data