O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

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

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