Re: pg_upgrade & tablespaces

As suggested I did a couple more experiments. This time I installed Postgres 9.0 in it's defauls location. I then installed Postgres 9.3 in /opt. Tested that both version booted up and ran independently of each other.

First test, Postgres 9.0 just after an initdb, so it's all clean. It completed successfully and created the analyze_new_cluster and delete_old_cluster scripts. So this was successful.

[pgsql@postgres-93-upgrade /tmp]$ time /opt/bin/pg_upgrade -d /usr/local/pgsql/data -D /usr/local/pgsql_93/data/ -b /usr/local/bin/ -B /opt/bin/ -p 5452 -P 5451   

Performing Consistency Checks

-----------------------------

Checking cluster versions                                   ok

Checking database user is a superuser                       ok

Checking for prepared transactions                          ok

Checking for reg* system OID user data types                ok

Checking for contrib/isn with bigint-passing mismatch       ok

Creating dump of global objects                             ok

Creating dump of database schemas

                                                            ok

Checking for presence of required libraries                 ok

Checking database user is a superuser                       ok

Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the

new cluster before continuing.

Performing Upgrade

------------------

Analyzing all rows in the new cluster                       ok

Freezing all rows on the new cluster                        ok

Deleting files from new pg_clog                             ok

Copying old pg_clog to new server                           ok

Setting next transaction ID for new cluster                 ok

Setting oldest multixact ID on new cluster                  ok

Resetting WAL archives                                      ok

Setting frozenxid counters in new cluster                   ok

Restoring global objects in the new cluster                 ok

Adding support functions to new cluster                     ok

Restoring database schemas in the new cluster

                                                            ok

Removing support functions from new cluster                 ok

Copying user relation files

                                                            ok

Setting next OID for new cluster                            ok

Sync data directory to disk                                 ok

Creating script to analyze new cluster                      ok

Creating script to delete old cluster                       ok

Upgrade Complete

----------------

Optimizer statistics are not transferred by pg_upgrade so,

once you start the new server, consider running:

    analyze_new_cluster.sh

Running this script will delete the old cluster's data files:

    delete_old_cluster.sh

real    0m8.141s

user    0m0.143s

sys     0m0.817s

Second test. I cleaned up the data folders for both installs and did initdb on both installs. This time I created one table space. It completed the upgrade, however it only created the analyze_new cluster script. No delete_old_cluster script. I created the symlink from the new install pointing to the old data folder, which is to be expected. But it left the old data there.

[pgsql@postgres-93-upgrade /tmp]$ time /opt/bin/pg_upgrade -d /usr/local/pgsql/data -D /usr/local/pgsql_93/data/ -b /usr/local/bin/ -B /opt/bin/ -p 5452 -P 5451   

Performing Consistency Checks

-----------------------------

Checking cluster versions                                   ok

Checking database user is a superuser                       ok

Checking for prepared transactions                          ok

Checking for reg* system OID user data types                ok

Checking for contrib/isn with bigint-passing mismatch       ok

Creating dump of global objects                             ok

Creating dump of database schemas

                                                            ok

Checking for presence of required libraries                 ok

Checking database user is a superuser                       ok

Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the

new cluster before continuing.

Performing Upgrade

------------------

Analyzing all rows in the new cluster                       ok

Freezing all rows on the new cluster                        ok

Deleting files from new pg_clog                             ok

Copying old pg_clog to new server                           ok

Setting next transaction ID for new cluster                 ok

Setting oldest multixact ID on new cluster                  ok

Resetting WAL archives                                      ok

Setting frozenxid counters in new cluster                   ok

Restoring global objects in the new cluster                 ok

Adding support functions to new cluster                     ok

Restoring database schemas in the new cluster

                                                            ok

Removing support functions from new cluster                 ok

Copying user relation files

                                                            ok

Setting next OID for new cluster                            ok

Sync data directory to disk                                 ok

Creating script to analyze new cluster                      ok

Upgrade Complete

----------------

Optimizer statistics are not transferred by pg_upgrade so,

once you start the new server, consider running:

    analyze_new_cluster.sh

Could not create a script to delete the old cluster's data

files because user-defined tablespaces exist in the old cluster

directory.  The old cluster's contents must be deleted manually.

real    0m9.865s

user    0m0.094s

sys     0m0.908s

[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/drupal_dbspace/

total 16

drwx------   4 pgsql  pgsql   4 Dec 26 15:49 .

drwx------  14 pgsql  pgsql  20 Dec 26 15:49 ..

drwx------   2 pgsql  pgsql   2 Dec 26 15:48 PG_9.0_201008051

drwx------   2 pgsql  pgsql   2 Dec 26 15:49 PG_9.3_201306121

[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql/data/pg_tblspc/

total 10

drwx------   2 pgsql  pgsql   4 Dec 26 15:48 .

drwx------  14 pgsql  pgsql  20 Dec 26 15:49 ..

lrwx------   1 pgsql  pgsql  36 Dec 26 15:48 16384 -> /usr/local/pgsql/data/drupal_dbspace

lrwx------   1 pgsql  pgsql  39 Dec 26 15:48 16385 -> /usr/local/pgsql/data/drupal_indexspace

[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_93/data/

PG_VERSION       global/          pg_hba.conf      pg_multixact/    pg_serial/       pg_stat/         pg_subtrans/     pg_twophase/     postgresql.conf  

base/            pg_clog/         pg_ident.conf    pg_notify/       pg_snapshots/    pg_stat_tmp/     pg_tblspc/       pg_xlog/         postmaster.opts  

[pgsql@postgres-93-upgrade /tmp]$ ls -la /usr/local/pgsql_93/data/pg_tblspc/

total 10

drwx------   2 pgsql  pgsql   4 Dec 26 15:49 .

drwx------  15 pgsql  pgsql  20 Dec 26 15:49 ..

lrwx------   1 pgsql  pgsql  36 Dec 26 15:49 16420 -> /usr/local/pgsql/data/drupal_dbspace

lrwx------   1 pgsql  pgsql  39 Dec 26 15:49 16421 -> /usr/local/pgsql/data/drupal_indexspace

While the upgrade was successful, I find it unusable and leaving me with a lot of manual labor ahead of me. Because it leaves the old folders there, which have to be deleted manually. The same with all the other data files, like postgresql.conf for example. Something that uninstalling 9.0 doesn't remove. In other words now I am left with a dirty /usr/local/pgsql/data folder and having to modify the postgres startup script. Or manually delete all the files and folders I don't want and reinstall Posgres 9.3 in the default location and create new symlinks.

I have asked a few people around here as to why we have so many table spaces, non seem to know the real reason. Some say it's to increase speed in table partitions. For example divided up by month and/or year. I don't thinks that reasoning would apply anymore these days. I think it was made the norm by our then Senior DBA 8 years ago and nobody questioned that since, they just kept on adding. In the end I think tablespaces are a pain.

-Joseph