Wednesday, June 6, 2012

Create a backup manifest in PostgreSQL with oid2name

oid2name is a nifty little program comes with postgresql an it allows you to take those obscure relfilenode named datafiles at the OS level and map them to database objects.

The only downside to this little tool is that it doesn't exactly work when your database is down since it uses pg_* to figure out this info. So if you're in a situation where you're restoring the database and find out that you're missing a file from the backup - you may want to know what table is lost.

If you thought ahead and have a manifest.txt in your backups you'll be able to see that the following maps to pgbench_accounts
-rw------- 1 postgres postgres 13434880 Jun  7 04:44 /db/9.1/data/base/1952441/1952454

If you use the -ix flag you get schema, OID and tablespace. If you also add the -S flag you get all the system tables too which is very important in a recovery situation.
/usr/pgsql-9.1/bin/oid2name -ix -d pgbench
From database "pgbench":
  Filenode             Table Name      Oid  Schema  Tablespace
   1952454       pgbench_accounts  1952448  public  pg_default
   1952459  pgbench_accounts_pkey  1952459  public  pg_default
   1952442       pgbench_branches  1952442  public  pg_default
   1952455  pgbench_branches_pkey  1952455  public  pg_default
   1952451        pgbench_history  1952451  public  pg_default
   1952445        pgbench_tellers  1952445  public  pg_default
   1952457   pgbench_tellers_pkey  1952457  public  pg_default

No comments:

Post a Comment

Web Statistics