Database Reference
In-Depth Information
Hot logical backup of all tables
in a tablespace
Sometimes we may wish to make a dump of tables and data in a tablespace. Unfortunately,
there isn't a simple command to do this, so we need to write some reusable scripts.
How to do it...
It is possible for a tablespace to contain objects from more than one database, so run the
following query to see which databases from which you need to dump:
SELECT datname
FROM pg_database
WHERE oid IN (
SELECT pg_tablespace_databases(ts.oid)
FROM pg_tablespace ts
WHERE spcname = 'mytablespacename');
The following procedure allows you to dump all tables that reside on one tablespace and
within
one
database only.
Create a file named
onets.sql
that contains the following SQL, which extracts the list of
tables in a tablespace:
SELECT 'pg_dump'
UNION ALL
SELECT '-t ' || spcname || '.' || relname
FROM pg_class t JOIN pg_tablespace ts
ON reltablespace = ts.oid AND spcname = :TSNAME
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE relkind = 'r'
UNION ALL
SELECT '-F c > dumpfile'; -- dumpfile is the name of the output file
Execute the query to build the
pg_dump
script:
psql -t -v TSNAME="'mytablespace'" -f onets.sql > get_my_ts
From the recovered database server, dump the tables in the tablespace, including data and
definitions. The output file is named "dumpile", from last line in the first step.
chmod 755 get_my_ts
./get_my_ts
