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)
