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 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:

  1. Create a file named onets.sql that contains the following SQL. This query extracts the list of tables in a ...

Get PostgreSQL 9 Administration Cookbook - Second Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial