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:
onets.sqlthat contains the following SQL. This query extracts the list of tables in a ...