O'Reilly logo

PostgreSQL 9 Administration Cookbook - Second Edition by Simon Riggs, Gianni Ciolli, Hannu Krosing, Gabriele Bartolini

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required