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 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 ...
No credit card required