Waiting for 9.0 – pg_upgrade

On May, 12ve, Bruce Momjian committed new contrib module for 9.0 – pg_upgrage.

As I understand – this is what was available before as pg-migrator.

If you're not familiar with it – it's a tool that allows upgrade of $PGDATA from some version to some version. What's the use case? Let's assume you have this 200GB database working as 8.3, and you'd like to go to 8.4 (or 9.0). Normal way is pg_dump + pg_restore – which will take some time. With pg-migrate/pg_upgrade it should be faster, and easier. So, let's play with it.

I have 2 versions of Pg installed:

  • 8.3.10
  • 9.0 (straight from git, introduces itself as 9.0beta1)

First, let's create test database in 8.3. Since I don't have the database yet (it's test setup), I'll create one:

=$ source pg-env.sh 8.3.10
Pg version 8.3.10 chosen
 
=$ mkdir /home/pgdba/data-8.3
 
=$ initdb -D /home/pgdba/data-8.3/ -E UTF-8
...
Success. You can now start the database server using:
...
 
=$ pg_ctl -D /home/pgdba/data-8.3 -l /home/pgdba/logfile-8.3 start
server starting

OK, here I have nice 8.3 working:

=$ psql -d postgres -c 'select version()'
                                             version
--------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.10 ON x86_64-unknown-linux-gnu, compiled BY GCC gcc (Ubuntu 4.4.3-4ubuntu5) 4.4.3
(1 ROW)

So, let's add some data to it:

# CREATE TABLE test ( i int4, j int8, t text, ts timestamptz, ip inet);
CREATE TABLE
 
# INSERT INTO test (i, j, t, ts, ip)
    SELECT
        random() * 1000000000,
        random() * 8000000000,
        repeat('depesz : ', CAST(5 + random() * 10 AS int4)),
        now() - random() * '5 years'::INTERVAL,
        '127.0.0.1'
    FROM generate_series(1, 10000000);
INSERT 0 10000000

OK. Data inside, let's see the data and some statistics, to be able to verify data after migration:

<code># SELECT * FROM test LIMIT 3;
     i     |     j      |                                                      t                                                       |              ts               |    ip     
-----------+------------+--------------------------------------------------------------------------------------------------------------+-------------------------------+-----------
 154912674 | 7280213505 | depesz : depesz : depesz : depesz : depesz : depesz : depesz : depesz :                                      | 2007-07-25 20:55:11.357501+02 | 127.0.0.1
 560106405 | 7676185891 | depesz : depesz : depesz : depesz : depesz : depesz : depesz : depesz : depesz : depesz : depesz : depesz :  | 2006-07-23 15:40:35.203901+02 | 127.0.0.1
 683442113 | 5831204534 | depesz : depesz : depesz : depesz : depesz : depesz : depesz : depesz :                                      | 2006-07-01 16:58:17.175101+02 | 127.0.0.1
(3 ROWS)</code>
 
# SELECT
    MIN(i), COUNT(DISTINCT i), MAX(i), SUM(i),
    MIN(j), COUNT(DISTINCT j), MAX(j), SUM(j),
    COUNT(DISTINCT t), SUM(LENGTH(t)),
    COUNT(DISTINCT ts),
    COUNT(*),
    pg_relation_size('test'),
    pg_total_relation_size('test')
FROM test;
-[ RECORD 1 ]------------------------------
MIN                    | 16
COUNT                  | 9948511
MAX                    | 999999620
SUM                    | 5001298277187874
MIN                    | 417
COUNT                  | 9976964
MAX                    | 7999999207
SUM                    | 40006841224204502
COUNT                  | 11
SUM                    | 900027558
COUNT                  | 9968978
COUNT                  | 10000000
pg_relation_size       | 1563410432
pg_total_relation_size | 1563418624

OK, Now, let's add some indexes to make sure that these are also working after migration:

# create index i1 on test (i);
CREATE INDEX

# create index i2 on test (j);
CREATE INDEX

# create index i3 on test (ts);
CREATE INDEX

# SELECT MIN(i), MAX(i), MIN(j), MAX(j), MIN(ts), MAX(ts) FROM test;
-[ RECORD 1 ]----------------------
MIN | 16
MAX | 999999620
MIN | 417
MAX | 7999999207
MIN | 2005-05-13 15:03:01.027901+02
MAX | 2010-05-12 15:02:48.586301+02
 
# EXPLAIN analyze SELECT MIN(i), MAX(i), MIN(j), MAX(j), MIN(ts), MAX(ts) FROM test;
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 RESULT  (cost=22.35..22.36 ROWS=1 width=0) (actual TIME=0.113..0.113 ROWS=1 loops=1)
   InitPlan
     ->  LIMIT  (cost=0.00..3.73 ROWS=1 width=20) (actual TIME=0.023..0.023 ROWS=1 loops=1)
           ->  INDEX Scan USING i1 ON test  (cost=0.00..37258844.64 ROWS=10000000 width=20) (actual TIME=0.022..0.022 ROWS=1 loops=1)
                 FILTER: (i IS NOT NULL)
     ->  LIMIT  (cost=0.00..3.73 ROWS=1 width=20) (actual TIME=0.008..0.008 ROWS=1 loops=1)
           ->  INDEX Scan Backward USING i1 ON test  (cost=0.00..37258844.64 ROWS=10000000 width=20) (actual TIME=0.007..0.007 ROWS=1 loops=1)
                 FILTER: (i IS NOT NULL)
     ->  LIMIT  (cost=0.00..3.72 ROWS=1 width=20) (actual TIME=0.012..0.012 ROWS=1 loops=1)
           ->  INDEX Scan USING i2 ON test  (cost=0.00..37236683.21 ROWS=10000000 width=20) (actual TIME=0.011..0.011 ROWS=1 loops=1)
                 FILTER: (j IS NOT NULL)
     ->  LIMIT  (cost=0.00..3.72 ROWS=1 width=20) (actual TIME=0.006..0.006 ROWS=1 loops=1)
           ->  INDEX Scan Backward USING i2 ON test  (cost=0.00..37236683.21 ROWS=10000000 width=20) (actual TIME=0.006..0.006 ROWS=1 loops=1)
                 FILTER: (j IS NOT NULL)
     ->  LIMIT  (cost=0.00..3.73 ROWS=1 width=20) (actual TIME=0.049..0.049 ROWS=1 loops=1)
           ->  INDEX Scan USING i3 ON test  (cost=0.00..37257725.52 ROWS=10000000 width=20) (actual TIME=0.048..0.048 ROWS=1 loops=1)
                 FILTER: (ts IS NOT NULL)
     ->  LIMIT  (cost=0.00..3.73 ROWS=1 width=20) (actual TIME=0.008..0.008 ROWS=1 loops=1)
           ->  INDEX Scan Backward USING i3 ON test  (cost=0.00..37257725.52 ROWS=10000000 width=20) (actual TIME=0.007..0.007 ROWS=1 loops=1)
                 FILTER: (ts IS NOT NULL)
 Total runtime: 0.192 ms
(21 ROWS)

OK. Everything seems to be working. Before I will actually try to migrate it, I will first make backup of 8.3 $PGDATA – just to be sure I don't have to re-generate the data.

=$ pg_ctl -D /home/pgdba/data-8.3/ stop
waiting for server to shut down.... done
server stopped
 
=$ rsync -a --delete --delete-after data-8.3/ data-8.3.backup/
 
=$ for a in data-8.3*; do printf "%-30s : " $a; find $a/ -type f -print | sort | xargs cat | md5sum -; done
data-8.3                       : a559d351b54f20ce66a0bb89a0724eb9  -
data-8.3.backup                : a559d351b54f20ce66a0bb89a0724eb9  -

So, now let's try to upgrade it to 9.0. According to docs I need first to create destination cluster. So let's do it:

=$ source pg-env.sh 9.0
Pg version 9.0 chosen
 
=$ initdb -D /home/pgdba/data-9.0
...
Success. You can now start the database server using:

Before I'll go, let's check md5sum of data-9.0:

=$ for a in data-9.0; do printf "%-30s : " $a; find $a/ -type f -print | sort | xargs cat | md5sum -; done
data-9.0                       : 930fdef9c4c48808a9dbabe8573b2d2c  -

Now, that I have both datadirs ready, and none of pg running (they can't be running during upgrade, I can:

=$ time pg_upgrade
    --old-datadir=/home/pgdba/data-8.3/ \
    --new-datadir=/home/pgdba/data-9.0/ \
    --old-bindir=/opt/pgsql-8.3.10/bin/ \
    --new-bindir=/opt/pgsql-9.0/bin/ \
    --old-port=5830 \
    --new-port=5900 \
    --user=pgdba
Performing Consistency Checks
-----------------------------
Checking old data directory (/home/pgdba/data-8.3)          ok
Checking new data directory (/home/pgdba/data-9.0)          ok
Checking for /contrib/isn with bigint-passing mismatch      ok
Checking for invalid 'name' user columns                    ok
Checking for tsquery user columns                           ok
Creating script to adjust sequences                         ok
Checking for large objects                                  ok
Creating catalog dump                                       ok
Checking for presence of required libraries                 ok
 
| If pg_upgrade fails after this point, you must
| re-initdb the new cluster before continuing.
| You will also need to remove the ".old" suffix
| from /home/pgdba/data-8.3/global/pg_control.old.
 
Performing Migration
--------------------
Adding ".old" suffix to old global/pg_control               ok
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting new commit clogs                                   ok
Copying old commit clogs to new server                      ok
Setting next transaction id for new cluster                 ok
Resetting WAL archives                                      ok
Setting frozenxid counters in new cluster                   ok
Creating databases in the new cluster                       ok
Adding support functions to new cluster                     ok
Restoring database schema to new cluster                    ok
Removing support functions from new cluster                 ok
Restoring user relation files
                                                            ok
Setting next oid for new cluster                            ok
Creating script to delete old cluster                       ok
Checking for tsvector user columns                          ok
Checking for hash and gin indexes                           ok
Checking for bpchar_pattern_ops indexes                     ok
Checking for large objects                                  ok
 
Upgrade complete
----------------
| Optimizer statistics and free space information
| are not transferred by pg_upgrade so consider
| running:
|       vacuumdb --all --analyze
| on the newly-upgraded cluster.
 
| Running this script will delete the old cluster's data files:
|       /home/pgdba/pg_upgrade_output/delete_old_cluster.sh
 
real    0m28.910s
user    0m0.160s
sys     0m5.660s

Quick check:

=$ for a in data-8.3* data-9.0; do printf "%-30s : " $a; find $a/ -type f -print | sort | xargs cat | md5sum -; done
data-8.3                       : 2d3e26f3e7363ec225fb1f9f93e45184  -
data-8.3.backup                : a559d351b54f20ce66a0bb89a0724eb9  -
data-9.0                       : 40ccb32c89acafb5436ea7dcd9f737a5  -

Shows that both source and destination have been modified. So, let's start the 9.0 and see the data:

=$ pg_ctl -D /home/pgdba/data-9.0 -l logfile-9.0 start
server starting

Of course – I should run vacuum as suggested by pg_upgrade:

=$ vacuumdb --all --analyze
vacuumdb: vacuuming database "postgres"
WARNING:  some databases have not been vacuumed in over 2 billion transactions
DETAIL:  You might have already suffered transaction-wraparound data loss.
vacuumdb: vacuuming database "template1"
WARNING:  some databases have not been vacuumed in over 2 billion transactions
DETAIL:  You might have already suffered transaction-wraparound data loss.

Above error looks bad, but this has been recently discussed on pgsql-hackers, and I believe it will be fixed before final 9.0 will be released.

What about our real data?

# SELECT
    MIN(i), COUNT(DISTINCT i), MAX(i), SUM(i),
    MIN(j), COUNT(DISTINCT j), MAX(j), SUM(j),
    COUNT(DISTINCT t), SUM(LENGTH(t)),
    COUNT(DISTINCT ts),
    COUNT(*),
    pg_relation_size('test'),
    pg_total_relation_size('test')
FROM test;
-[ RECORD 1 ]----------+------------------
MIN                    | 16
COUNT                  | 9948511
MAX                    | 999999620
SUM                    | 5001298277187874
MIN                    | 417
COUNT                  | 9976964
MAX                    | 7999999207
SUM                    | 40006841224204502
COUNT                  | 11
SUM                    | 900027558
COUNT                  | 9968978
COUNT                  | 10000000
pg_relation_size       | 1563410432
pg_total_relation_size | 2237767680

All values are the same except for pg_total_relation_size, but that's because I added indexes on 8.3 before getting pg_total_relation_size(), so it's fine.

Now. Will the indexes be used?

# EXPLAIN analyze SELECT MIN(i), MAX(i), MIN(j), MAX(j), MIN(ts), MAX(ts) FROM test;
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 RESULT  (cost=22.36..22.37 ROWS=1 width=0) (actual TIME=0.319..0.319 ROWS=1 loops=1)
   InitPlan 1 (RETURNS $0)
     ->  LIMIT  (cost=0.00..3.73 ROWS=1 width=20) (actual TIME=0.066..0.077 ROWS=1 loops=1)
           ->  INDEX Scan USING i1 ON test  (cost=0.00..37258941.16 ROWS=10000000 width=20) (actual TIME=0.039..0.039 ROWS=1 loops=1)
                 INDEX Cond: (i IS NOT NULL)
   InitPlan 2 (RETURNS $1)
     ->  LIMIT  (cost=0.00..3.73 ROWS=1 width=20) (actual TIME=0.042..0.051 ROWS=1 loops=1)
           ->  INDEX Scan Backward USING i1 ON test  (cost=0.00..37258941.16 ROWS=10000000 width=20) (actual TIME=0.023..0.023 ROWS=1 loops=1)
                 INDEX Cond: (i IS NOT NULL)
   InitPlan 3 (RETURNS $2)
     ->  LIMIT  (cost=0.00..3.73 ROWS=1 width=20) (actual TIME=0.039..0.046 ROWS=1 loops=1)
           ->  INDEX Scan USING i2 ON test  (cost=0.00..37258751.12 ROWS=10000000 width=20) (actual TIME=0.025..0.025 ROWS=1 loops=1)
                 INDEX Cond: (j IS NOT NULL)
   InitPlan 4 (RETURNS $3)
     ->  LIMIT  (cost=0.00..3.73 ROWS=1 width=20) (actual TIME=0.031..0.037 ROWS=1 loops=1)
           ->  INDEX Scan Backward USING i2 ON test  (cost=0.00..37258751.12 ROWS=10000000 width=20) (actual TIME=0.018..0.018 ROWS=1 loops=1)
                 INDEX Cond: (j IS NOT NULL)
   InitPlan 5 (RETURNS $4)
     ->  LIMIT  (cost=0.00..3.73 ROWS=1 width=20) (actual TIME=0.016..0.017 ROWS=1 loops=1)
           ->  INDEX Scan USING i3 ON test  (cost=0.00..37259259.58 ROWS=10000000 width=20) (actual TIME=0.015..0.015 ROWS=1 loops=1)
                 INDEX Cond: (ts IS NOT NULL)
   InitPlan 6 (RETURNS $5)
     ->  LIMIT  (cost=0.00..3.73 ROWS=1 width=20) (actual TIME=0.009..0.009 ROWS=1 loops=1)
           ->  INDEX Scan Backward USING i3 ON test  (cost=0.00..37259259.58 ROWS=10000000 width=20) (actual TIME=0.009..0.009 ROWS=1 loops=1)
                 INDEX Cond: (ts IS NOT NULL)
 Total runtime: 0.403 ms
(26 ROWS)

Yes. It works.

Currently there are some rough edges, namely:

  • something about transaction wraparound and template0
  • lack of docs in .html format

but I believe these will be soon fixed. And speed it pretty impressive – 2.2GB of $PGDATA converted in 29 seconds is pretty cool.

8 thoughts on “Waiting for 9.0 – pg_upgrade”

  3. Thanks so much for featuring pg_upgrade on your blog.

    I have just fixed the xid wraparound bug in both PG CVS and in pg_migrator on pgFoundry.

    There are HTML docs for pg_upgrade that are with the other /contrib modules,

    You can certainly use pg_upgrade for migrations from alpha/beta to final.

  4. @Bruce:
    Thanks for reading my blog 🙂

    As for docs – sorry, must have had too early checkout. And thanks for clarification of my mistake about upgrade from alpha/beta. Great stuff.

  5. How good it works with tablespaces? that is where we have tablespaces on different disks then the data folder itself

  7. Suppose you’ve a server w/ floating point datetimes to upgrade. Currently, integer datetimes are default; correct? Will pg_upgrade handle the change for you or will a dump/reload still be required?

Comments are closed.