Postgresql: Recovering a single table in Postgresql when the database is backup with text format

As the subject stated, recovering a single when database was backup with text format.

1 - open the text format backup file.
2 - search for the table. In this example, I am using a fake table (vpx_version) to simulate this. 
3 - copy and paste them to excel spreadsheet. Remember to have the header.
4- save the excel spreadsheet in "CSV" format on DOS.
5- move the csv file to the location of where you want to load the data into.

  

  

Note: the table structure must already exist for COPY to work, it does not create the table. If you dropped the table instead of truncated or delete without where clause, you will need to create the table manually. The HEADER flag is when you actually have a header if you do not, it might bomb out depending on the type of data you have defined at the base table.

vcdb=> select * from vpx_version;

ver_id |       version_value

--------+----------------------------

    600 | VirtualCenter Database 6.0

(1 row)

vcdb=> truncate table vpx_version;

TRUNCATE TABLE

vcdb=> select * from vpx_version;

ver_id | version_value

--------+---------------

(0 rows)

vcdb=> \COPY vpx_version from test.csv CSV HEADER;

vcdb=> select * from vpx_version;

ver_id |       version_value

--------+----------------------------

    600 | VirtualCenter Database 6.0

    601 | VirtualCenter Database 6.0

    602 | VirtualCenter Database 6.0

    603 | VirtualCenter Database 6.0

    604 | VirtualCenter Database 6.0

    605 | VirtualCenter Database 6.0

(6 rows)

If there are options, I will urge user to do backup of the database with pg_dump -Fc or "create table" .. "from select all".

##################################


Well that's all .. ... restoring my vpx_version table to original state ...


postgres=# \c vcdb vc
You are now connected to database "vcdb" as user "vc".
vcdb=> select * from vpx_version;
 ver_id |       version_value
--------+----------------------------
    600 | VirtualCenter Database 6.0
    601 | VirtualCenter Database 6.0
    602 | VirtualCenter Database 6.0
    603 | VirtualCenter Database 6.0
    604 | VirtualCenter Database 6.0
    605 | VirtualCenter Database 6.0
(6 rows)

vcdb=> truncate table vpx_version;
TRUNCATE TABLE

vcdb=> select * from vpx_version;
 ver_id | version_value
--------+---------------
(0 rows)


vcdb=>

postgres@localhost:~> pg_restore -U vc -d vcdb -a -t vpx_version vcdb.Fc.backup

postgres@localhost:~> psql
psql.bin (9.3.6 (VMware Postgres 9.3.6.0-2686691 release))
Type "help" for help.

postgres=# \c vcdb vc

You are now connected to database "vcdb" as user "vc".

vcdb=> select * from vpx_version;
 ver_id |       version_value
--------+----------------------------
    600 | VirtualCenter Database 6.0

(1 row)