May 19th, 2010 by depesz | Tags: , , , , , , , | 7 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

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.

  1. 7 comments

  2. May 19, 2010

    Can pg_upgrade be used to updated a _beta or _RC installation to a major release?

    For example can 9.0b1 be upgraded to 9.0?

  3. May 19, 2010

    @Richard:
    I don’t know for sure, but I definitely doubt that.

  4. May 19, 2010

    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.

  5. May 19, 2010

    @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.

  6. May 20, 2010

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

  7. May 24, 2010

    Shoaib, tablespaces work fine. I even create a script to delete your old tablespaces and data directory for you.

  8. # Mike Adams
    May 27, 2010

    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?

Leave a comment