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(at)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(at)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(at)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(at)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(at)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(at)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