About postgresql--Common operating instructions

This article is reproduced from http://blog.chinaunix.net/uid-22920230-id-3493064.html

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,
Cases:

/usr/local/pgsql/bin/psql MyDB

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

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:

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":

To export data for a table, add the parameter "-a":

To view a sequence:

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

To view the database size:

To view the size of a table:

About postgresql--Common operating instructions