Search Postgresql Archives

Re: pg_upgrade & tablespaces

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux