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.
It is possible for a tablespace to contain objects from more than one database, so run the following query to see from which databases 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 a given tablespace and within one database only:
Create a file named
onets.sqlthat contains the following SQL. This query extracts the list of tables in a tablespace:SELECT 'pg_dump ' || array_to_string( array_agg('-t ' || n.nspname || '.' || t.relname) , ' ') || ' -F c ' || :'DBNAME' || ' > dumpfile_' || :'DBNAME' -- name of the output file FROM pg_class t JOIN pg_tablespace ts ON ts.oid...