PostgreSQL Import Export Table Example

Source: Internet
Author: User
Tags create index postgresql psql table name create database


Export table

Pg_dump-h localhost-u postgres (user name) database name (with user name by default)-T table (table name) > Dump.sql

Import Table

Psql-f Dump.sql


Example of a complete import export table

1, check the original database

-bash-3.2$ psql-u playboy-d Playboy//Original database
Welcome to Psql 8.1.23, the PostgreSQL Interactive terminal.
Type: \copyright for distribution Terms
\h for help with SQL commands
\? For help with Psql commands
\g or terminate with semicolon to execute query
\q to quit

Playboy=> \dt;
List of relations
Schema | Name |  Type | Owner
--------+------------+-------+---------
Public | Contents | Table | Playboy
Public | Entries | Table | Playboy
Public | Properties | Table | Playboy
Public | Settings | Table | Playboy
Public | Test | Table | Playboy
(5 rows)

Playboy=> \q


2, Export database and table


1.-bash-3.2$ pg_dump-o Playboy >/var/lib/pgsql/data/playboy2013.sql     // Export Playboy database   
2. 
3.-bash-3.2$ pg_dumpall >/var/lib/pgsql/data/all_databases2013.sql        //Export all databases   
4. 
5.-bash-3.2$ pg_dump-o playboy-ft -T Test >/var/lib/pgsql/data/playboy_test2013.tar  //Export a table. tar files for pg_restore  
6.  
7.-bash-3.2$ ls/var/lib/pgsql/data |grep 2013                              //View the guide   
8.playboy2013.sql all_databases2013.sql playboy_test2013.tar 
-bash-3.2$ Pg_dump-o Playboy >/var/lib/pgsql/data/playboy2013.sql     //Export Playboy database

-bash-3.2$ pg_dumpall >/var/lib/pgsql/data/all_databases2013.sql//Export all databases

-bash-3.2$ pg_dump-o playboy-ft-t Test >/var/lib/pgsql/data/playboy_test2013.tar//Export a table. tar files for Pg_restore

-bash-3.2$ ls/var/lib/pgsql/data |grep 2013//Check it out, guide.
Playboy2013.sql All_databases2013.sql Playboy_test2013.tar3, create a new database, and import


 
-bash-3.2$ psql-u playboy-d playboy          & nbsp;                         //Original database
Welcome to Psql 8.1.23, the PostgreSQL Interactive terminal.
type:  \copyright for distribution Terms
       \h to help with SQL commands
& nbsp;      \? For help with psql commands
       \g or terminate with semicolon to execute query
& nbsp;      \q to quit

Playboy=> \dt;
List of relations
Schema | Name |  Type | Owner
--------+------------+-------+---------
Public | Contents | Table | Playboy
Public | Entries | Table | Playboy
Public | Properties | Table | Playboy
Public | Settings | Table | Playboy
Public | Test | Table | Playboy
(5 rows)

Playboy=> \q

-bash-3.2$ createdb playboy_test-o Playboy//Create a database belonging to Playboy Playboy_test
CREATE DATABASE

-bash-3.2$ pg_restore-d Playboy_test/var/lib/pgsql/data/playboy_test2013.tar//Import single table,

After the import table is deleted, the Playboy database is imported into the Playboy_test, and the permissions belong to Playboy
-bash-3.2$ psql-d playboy_test-u playboy-f/var/lib/pgsql/data/playboy2013.sql
SET
SET
SET
COMMENT
SET
CREATE SEQUENCE
Setval
--------
18
(1 row)

SET
SET
CREATE TABLE
CREATE SEQUENCE
Setval
--------
4
(1 row)

CREATE TABLE
CREATE TABLE
CREATE SEQUENCE
Setval
--------
3
(1 row)

CREATE TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT

-bash-3.2$ psql-u playboy-d playboy_test//Login to Playboy_test
Welcome to Psql 8.1.23, the PostgreSQL Interactive terminal.

type:  \copyright for distribution Terms
       \h to help with SQL commands       ? For help with psql commands
       \g or terminate with semicolon to execute query
& nbsp;      \q to quit

Playboy_test=> \dt; Look at the table, the same as the root Playboy database.
List of relations
Schema | Name |  Type | Owner
--------+------------+-------+----------
Public | Contents | Table | Playboy
Public | Entries | Table | Playboy
Public | Properties | Table | Playboy
Public | Settings | Table | Playboy
Public | Test | Table | Playboy

(5 rows)


Pgsql Import write more, above already has two kinds of, say a kind of


1.-bash-3.2$ psql-u Playboy Playboy_test </var/lib/pgsql/data/playboy2013.sql

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.