Upgrade postgres to latest feasible version

freelawproject / courtlistener

A fully-searchable and accessible archive of court data including growing repositories of opinions, oral arguments, judges, judicial financial records, and federal filings.

https://www.courtlistener.com

Other

548 stars 150 forks source link

Upgrade postgres to latest feasible version #890

Closed mlissner closed 5 years ago

mlissner commented 6 years ago

We're currently using postgres 9.3, but for a client we want to use AWS's migration/replication feature over at:

https://aws.amazon.com/dms/

(They want to have unfettered access to our latest DB.)

That requires that we have at least version 9.4, so we have to at least upgrade to that.

The Postgres folks kindly have an apt repository that supports our version of Ubuntu, so this shouldn't actually be too terrible. Last time we did this, we documented our work over here: https://github.com/freelawproject/courtlistener/issues/288

A couple questions, though:

Is there any reason not to upgrade all the way to the latest version?
Do we need to run pg_upgradecluster for every point version we want to upgrade, or can we just do it once and skip a bunch of steps?
How much downtime can we expect for this?

mlissner commented 6 years ago

↪ sudo pg_lsclusters 
[sudo] password for mlissner: 
Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
04:17:58::mlissner@new-courtlistener::~ 
↪ ncdu /var/lib/postgresql/9.3/main
04:18:16::mlissner@new-courtlistener::~ 
↪ sudo !!
sudo ncdu /var/lib/postgresql/9.3/main

Our cluster is currently about 250GB.

mlissner commented 6 years ago

Looks like you can skip versions when doing the upgrade. I didn't find anything explicitly saying that, and I wanted to find some sort of version compatibility matrix chart to really prove this to myself, but lots of examples are around of people skipping versions.

I checked if Django has any recommendations about what versions it supports. It says 9.4+ and the latest of psychopg, so that all sounds good.

I think I can also upgrade to the latest version of postgres, which is supposed to have vastly improved performance, so I'll try that after reading the release notes.

The last big question/issue is that the cluster is about 250GB and we only have about 200GB free on the server. So if we use pg_upgrade, with the --link flag, then it works by upgrading the cluster in place, but if something goes haywire, there's no backup except for our latest restic backup....which...not great. Ideally I'll find 50GB to muck around with so that we don't have to do an in-place upgrade.

mlissner commented 6 years ago

The 9.4 release notes say:

Add support for logical decoding of WAL data, to allow database changes to be streamed out in a customizable format

So that's why AWS requires 9.4, I suspect.

mlissner commented 6 years ago

The 9.5 release notes say:

Replace configuration parameter checkpoint_segments with min_wal_size and max_wal_size (Heikki Linnakangas)

If you previously adjusted checkpoint_segments, the following formula will give you an approximately equivalent setting:

max_wal_size = (3 checkpoint_segments) 16MB

Note that the default setting for max_wal_size is much higher than the default checkpoint_segments used to be, so adjusting it might no longer be necessary.

Our value for checkpoint_segments is currently set to 32. Using the formula above, we'd want max_wal_size to be 3 32 16MB --> 1536MB. The new default is now 1GB, so I think we can just use that value and it should be fine. We don't need to tinker with min_wal_size.

mlissner commented 6 years ago

The 10.0 release notes say:

Remove server support for client/server protocol version 1.0 (Tom Lane)

This protocol hasn't had client support since PostgreSQL 6.3.

6.3 came out in...1998. This makes me feel OK about a major upgrade. Still have my guard up, but damn.

mlissner commented 6 years ago

OK, I've read through all of the release notes for major versions. The minor version notes are another 80% of the notes and all of the ones I read were just "fix this, fix that." So I feel OK skipping these.

Version 11 came out a week ago and has some pretty big stuff in it without much in the way of advantages. The only nice feature it has that we'd benefit from is that it allows fast creation of new columns with default values. I think previously these required a table rewrite. I'm not positive of that, but I know they were SLOW.

So if we can live without the feature, and if we assume version 11 is just a little too new, I think we can go all the way to 10.5 without any issues aside from the one I mentioned above.

dmichalopoulos commented 6 years ago

Ok great!!

On Oct 26, 2018, at 1:43 AM, Mike Lissner notifications@github.com wrote:

OK, I've read through all of the release notes for major versions. The minor version notes are another 80% of the notes and all of the ones I read were just "fix this, fix that."

Version 11 came out a week ago and has some pretty big stuff in it without much in the way of advantages. The only nice feature it has that we'd benefit from is that it allows fast creation of new columns with default values. I think previously these required a table rewrite. I'm not positive of that, but I know they were SLOW.

So if we can live without the feature, and if we assume version 11 is just a little too new, I think we can go all the way to 10.5 without any issues.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/freelawproject/courtlistener/issues/890#issuecomment-433303901, or mute the thread https://github.com/notifications/unsubscribe-auth/AqZOEgeWZ1JYNk1Ap_Ulmz0j8dd43jyoks5uoq8VgaJpZM4X5OOb.

mlissner commented 6 years ago

Step one: Add the apt repo:

Create a file at /etc/apt/sources.list.d/pgdg.list, and then add:
```
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main
```

Add the key with:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Then update with the normal:
```
sudo apt-get update
```

That worked. Next step: Install postgres according to our ansible scripts.

02:59:38::mlissner@new-courtlistener::/etc/apt/sources.list.d 
↪ sudo apt-get install postgresql-10 postgresql-contrib-10
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Note, selecting 'postgresql-10' instead of 'postgresql-contrib-10'
The following packages were automatically installed and are no longer required:
  comerr-dev krb5-multidev libgssrpc4 libkadm5clnt-mit9 libkadm5srv-mit9
  libkdb5-7
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
  libpq-dev libpq5 pgdg-keyring postgresql-client-10 postgresql-client-common
  postgresql-common
Suggested packages:
  postgresql-doc-11 locales-all postgresql-doc-10 libjson-perl
The following NEW packages will be installed:
  pgdg-keyring postgresql-10 postgresql-client-10
The following packages will be upgraded:
  libpq-dev libpq5 postgresql-client-common postgresql-common
4 upgraded, 3 newly installed, 0 to remove and 112 not upgraded.
Need to get 6,897 kB of archives.
After this operation, 29.3 MB of additional disk space will be used.
Do you want to continue? [Y/n] 
Get:1 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main libpq-dev amd64 11.0-1.pgdg14.04+2 [159 kB]
Get:2 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main libpq5 amd64 11.0-1.pgdg14.04+2 [163 kB]
Get:3 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main pgdg-keyring all 2018.1+b1 [10.9 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main postgresql-client-common all 195.pgdg14.04+1 [83.2 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main postgresql-client-10 amd64 10.5-2.pgdg14.04+1 [1,348 kB]
Get:6 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main postgresql-common all 195.pgdg14.04+1 [223 kB]
Get:7 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main postgresql-10 amd64 10.5-2.pgdg14.04+1 [4,910 kB]
Fetched 6,897 kB in 2s (2,630 kB/s)  
Preconfiguring packages ...
(Reading database ... 144137 files and directories currently installed.)
Preparing to unpack .../libpq-dev_11.0-1.pgdg14.04+2_amd64.deb ...
Unpacking libpq-dev (11.0-1.pgdg14.04+2) over (9.3.24-0ubuntu0.14.04) ...
Preparing to unpack .../libpq5_11.0-1.pgdg14.04+2_amd64.deb ...
Unpacking libpq5:amd64 (11.0-1.pgdg14.04+2) over (9.3.24-0ubuntu0.14.04) ...
Selecting previously unselected package pgdg-keyring.
Preparing to unpack .../pgdg-keyring_2018.1+b1_all.deb ...
Unpacking pgdg-keyring (2018.1+b1) ...
Preparing to unpack .../postgresql-client-common_195.pgdg14.04+1_all.deb ...
Unpacking postgresql-client-common (195.pgdg14.04+1) over (154ubuntu1.1) ...
Selecting previously unselected package postgresql-client-10.
Preparing to unpack .../postgresql-client-10_10.5-2.pgdg14.04+1_amd64.deb ...
Unpacking postgresql-client-10 (10.5-2.pgdg14.04+1) ...
Preparing to unpack .../postgresql-common_195.pgdg14.04+1_all.deb ...
Leaving 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (195.pgdg14.04+1) over (154ubuntu1.1) ...
Selecting previously unselected package postgresql-10.
Preparing to unpack .../postgresql-10_10.5-2.pgdg14.04+1_amd64.deb ...
Unpacking postgresql-10 (10.5-2.pgdg14.04+1) ...
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
Processing triggers for ureadahead (0.100.0-16) ...
Setting up libpq5:amd64 (11.0-1.pgdg14.04+2) ...
Setting up libpq-dev (11.0-1.pgdg14.04+2) ...
Setting up pgdg-keyring (2018.1+b1) ...
Removing apt.postgresql.org key from trusted.gpg: OK
Setting up postgresql-client-common (195.pgdg14.04+1) ...
Installing new version of config file /etc/postgresql-common/user_clusters ...
Setting up postgresql-client-10 (10.5-2.pgdg14.04+1) ...
update-alternatives: using /usr/share/postgresql/10/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-common (195.pgdg14.04+1) ...
Installing new version of config file /etc/init.d/postgresql ...
Replacing config file /etc/postgresql-common/createcluster.conf with new version
 * Starting PostgreSQL 9.3 database server                                    [ OK ] 
Processing triggers for ureadahead (0.100.0-16) ...
Setting up postgresql-10 (10.5-2.pgdg14.04+1) ...
Creating new PostgreSQL cluster 10/main ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5433 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
update-alternatives: using /usr/share/postgresql/10/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
 * Starting PostgreSQL 10 database server                                     [ OK ] 
Processing triggers for libc-bin (2.19-0ubuntu6.14) ...

Seems to have worked. Old DB is still operating.

mlissner commented 6 years ago

Had to update /etc/postgresql/10/main/pg_hba.conf to set up remote authentication.

[x] Test that remote server can access postgres after upgrade.

mlissner commented 6 years ago

Had to update settings in /etc/postgresql/10/main/postgresql.conf. Used vimdiff to compare against the old version, and used #385 as a guideline for solving ambiguities. Pretty straightforward. Accounted for the deprecation of checkpoint_segments, and added slow query logging using log_min_duration_statement. (This should have been enabled ages ago.)

I also went through the remainder of the files in /etc/postgresql/9.3/main/ and diffed them. No changes in other files.

mlissner commented 6 years ago

Check if new clusters were created (they were):

↪ sudo pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
10  main    5432 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

No need to run initdb (yay!).

meangrape commented 6 years ago

Just saw this. I'm an experienced PostgreSQL DBA and am always willing to help you out. (I provide advice to @harvard-lil about their PG databases). Don't hesitate to reach out in the future if you have any questions or need some assistance. Email is the best way to reach me; jay@meangrape.com.

On Fri, Oct 26, 2018 at 7:18 PM Mike Lissner notifications@github.com wrote:

Check if new clusters were created (they were):

↪ sudo pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 9.3 main 5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log 10 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/freelawproject/courtlistener/issues/890#issuecomment-433567583, or mute the thread https://github.com/notifications/unsubscribe-auth/AAAje8WBzoiu6nyRz4PP3cuvTI3tjyZ4ks5uo5hZgaJpZM4X5OOb .

-- Jay Edwards | @meangrape | jay@meangrape.com

mlissner commented 6 years ago

Had to stop the new server:

04:28:00::mlissner@new-courtlistener::/tmp 
↪ sudo -u postgres /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile stop
waiting for server to shut down.... done
server stopped

Then tried to run the checks with:

↪ sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \
>   --old-datadir /var/lib/postgresql/9.3/main \
>   --new-datadir /var/lib/postgresql/10/main \
>   --old-bindir /usr/lib/postgresql/9.3/bin \
>   --new-bindir /usr/lib/postgresql/10/bin \
>   --old-port 5432 \
>   --new-port 5433 \
>   --jobs 24 \
>   --check \
>   --verbose

That output a bunch of stuff (--verbose):

04:28:13::mlissner@new-courtlistener::/tmp 
↪ sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade   --old-datadir /var/lib/postgresql/9.3/main   --new-datadir /var/lib/postgresql/10/main   --old-bindir /usr/lib/postgresql/9.3/bin   --new-bindir /usr/lib/postgresql/10/bin   --old-port 5432   --new-port 5433   --jobs 24   --check   --verbose
Running in verbose mode
"/usr/lib/postgresql/9.3/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.3/main" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000  -c listen_addresses='' -c unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           6213349950994991580
Database cluster state:               in production
pg_control last modified:             Fri Oct 26 16:27:56 2018
Latest checkpoint location:           679/D7489F88
Prior checkpoint location:            679/D1FD60E0
Latest checkpoint's REDO location:    679/D234C8F0
Latest checkpoint's REDO WAL file:    0000000100000679000000D2
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/518649262
Latest checkpoint's NextOID:          62246198
Latest checkpoint's NextMultiXactId:  1757730
Latest checkpoint's NextMultiOffset:  7176800
Latest checkpoint's oldestXID:        323612475
Latest checkpoint's oldestXID's DB:   16385
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 16385
Time of latest checkpoint:            Fri Oct 26 16:23:26 2018
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            minimal
Current max_connections setting:      250
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Current pg_control values:

pg_control version number:            1002
Catalog version number:               201707211
Database system identifier:           6616790098772388733
Latest checkpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:555
Latest checkpoint's NextOID:          12972
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        548
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

Values to be changed:

First log segment after reset:        000000010000000000000002
connection to database failed: fe_sendauth: no password supplied
Failure, exiting

Didn't work because of a password issue. This is a messy situation. Ubuntu's postgres user doesn't have a password by default and relies on peer authentication in pg_hba.conf. When that config is set, you can login without a password. Alas, I swapped that to md5 authentication, and thus couldn't log in to change the password because none had ever been set. But it was thinking there should be one because of md5. So the fix is to edit pg_hba.conf to set peer authentication, as described here, restart things with:

sudo -u postgres /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile stop

and

sudo -u postgres /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main start -o "-c config_file=/etc/postgresql/10/main/postgresql.conf"

Then, set the password with (precede this with a space to prevent it from going into bash history):

sudo -u postgres psql -p 5433 -c "ALTER USER postgres WITH PASSWORD 'long-password';"

Stop things again, as above. Put the authentication back to md5, then start things again!

With that done, you should be able to run the check again with the command from above:

sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade   --old-datadir /var/lib/postgresql/9.3/main   --new-datadir /var/lib/postgresql/10/main   --old-bindir /usr/lib/postgresql/9.3/bin   --new-bindir /usr/lib/postgresql/10/bin   --old-port 5432   --new-port 5433   --jobs 24   --check   --verbose

That...alas, doesn't work, so half an hour of trial and error later, we arrive at:

↪ sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade   --old-datadir /var/lib/postgresql/9.3/main   --new-datadir /var/lib/postgresql/10/main   --old-bindir /usr/lib/postgresql/9.3/bin   --new-bindir /usr/lib/postgresql/10/bin   --old-port 5432   --new-port 5433   --jobs 24   --check --new-options "-c config_file=/etc/postgresql/10/main/postgresql.conf" --old-options "-c config_file=/etc/postgresql/9.3/main/postgresql.conf"

Which reports:

Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "unknown" user columns                 ok
Checking for hash indexes                                   ok
Checking for roles starting with "pg_"                      ok
Checking for incompatible "line" data type                  ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

Great. I guess we're ready to go, except for one thing: We don't have enough disk space. Tomorrow's problem, I suppose.

mlissner commented 6 years ago

@jayed, that's a super generous offer! How the heck did you find this issue!? I'm planning on doing this upgrade tomorrow and I'd love any help I could get between now and then. I think it should go smoothly, but it would be great to have a quick chat to confirm things. I'm not much of a DBA, in case my logs here haven't made that clear...

dmichalopoulos commented 6 years ago

@mlissner, nice work so far. Sounds like you’ve about got it figured out, and have the disk space issue to deal with. Is that the last obstacle at this point?

mlissner commented 6 years ago

Yep, I think that's it and I'm about to tackle that. Expect more updates here as I make progress tonight.

mlissner commented 6 years ago

OK, just cleared out 140GB by moving some source data to our warm storage instead of our hot disks. Good good, here we go.

mlissner commented 6 years ago

OK, overall process is:

[x] Stop Celery on all machines: sudo service celeryd stop
[x] Stop supervisord processes on all machines (this stops some bulk upload types of jobs)
[x] Redirect Apache to our maintenance page
[x] Start a screen session in case of network failure
- [x] Stop old database
- [x] Do the database upgrade
[x] Test the database with a few queries
- [x] Ensure access from remote servers
[x] Put apache back
[x] Restart supervisord & celery

mlissner commented 6 years ago

OK, upgrade complete:

↪ sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade   --old-datadir /var/lib/postgresql/9.3/main   --new
-datadir /var/lib/postgresql/10/main   --old-bindir /usr/lib/postgresql/9.3/bin   --new-bindir /usr/lib/postgres
ql/10/bin   --old-port 5432   --new-port 5433   --jobs 24  --new-options "-c config_file=/etc/postgresql/10/main
/postgresql.conf" --old-options "-c config_file=/etc/postgresql/9.3/main/postgresql.conf"
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "unknown" user columns                 ok
Checking for roles starting with "pg_"                      ok
Checking for incompatible "line" data type                  ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  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 in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the 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
Checking for hash indexes                                   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

(note that "Copying user relation files" took 95% of the time.)

Now let's start it up and see what we have.

mlissner commented 6 years ago

Next up:

I swapped the port in the postgresql.conf file.
I started it using the start command above.
I tested using dbshell on a remote and a local server
I tested using psql, then listing databases, connecting to courtlistener, and doing a query
I stopped it using the command above
I started it using `sudo service postgresql start 10

I'm currently running:

sudo -u postgres ./analyze_new_cluster.sh

This will generate new statistics so that queries can be well-optimized.

mlissner commented 6 years ago

OK, no big deal:

↪ sudo -u postgres ./analyze_new_cluster.sh 
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy.  When it is done, your system will
have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel
this script and run:
    "/usr/lib/postgresql/10/bin/vacuumdb" --all --analyze-only

vacuumdb: processing database "courtlistener": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "courtlistener": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "courtlistener": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics

Done

mlissner commented 6 years ago

OK, um...

celery is up on all servers
apache is up
supervisor daemons are up

All is well!

Remaining todos:

[x] Delete the old cluster (no rush, we've got 100GB of space), using the script provided
[x] Uninstall 9.3 using Ubuntu's methods (or else our init script will try to start it all the time, leading to...bad results.). I've switched it to port 5433 just in case so there's no port conflict, though it'd be annoying if it got started.
[x] Check if this made a measurable impact on munin — there are supposed to be performance enhancements, right?

I think that'll be it, but I'll review the remaining items tomorrow or Monday. I think this is a total success.

mlissner commented 6 years ago

Another couple todos:

[x] Update psql on all servers
[x] Update our docker image to have postgres 10
[x] Update the backup dump script so it has the right password

mlissner commented 6 years ago

The delete script just contained:

#!/bin/sh

rm -rf '/var/lib/postgresql/9.3/main'

Nothing too mystical there...

mlissner commented 6 years ago

I was able to update psql on all servers adding this:

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

To /etc/apt/sources.list.d/pgdg.list, then running:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-client-10

I tested this by running the psql command, which threw a warning when connected to a newer DB than itself.

mlissner commented 6 years ago

Managed to get postgres uninstalled with:

12:49:28::mlissner@new-courtlistener::/etc/postgresql/10/main 
↪ sudo apt-get remove postgresql-9.3 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages will be REMOVED:
  postgresql postgresql-9.3
0 upgraded, 0 newly installed, 2 to remove and 112 not upgraded.
After this operation, 11.7 MB disk space will be freed.
Do you want to continue? [Y/n] y
(Reading database ... 144879 files and directories currently installed.)
Removing postgresql (9.3+154ubuntu1.1) ...
Removing postgresql-9.3 (9.3.24-0ubuntu0.14.04) ...
 * Stopping PostgreSQL 9.3 database server                                                                 * Error: /var/lib/postgresql/9.3/main is not accessible or does not exist
                                                                                                   [fail]
invoke-rc.d: initscript postgresql, action "stop" failed.
dpkg: error processing package postgresql-9.3 (--remove):
 subprocess installed pre-removal script returned error exit status 1
E: Sub-process /usr/bin/dpkg returned an error code (1)
12:49:58::mlissner@new-courtlistener::/etc/postgresql/10/main 
↪ cd /var/lib/postgresql/9.3/
12:50:20::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ ls
total 0
12:50:21::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ mkdir main
mkdir: cannot create directory ‘main’: Permission denied
12:51:14::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ sudo !!
sudo mkdir main
12:51:16::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ ls
total 4.0K
drwxr-xr-x 2 root 4.0K Nov  9 12:51 main/
12:51:17::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ sudo apt-get remove postgresql-9.3 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages will be REMOVED:
  postgresql-9.3
0 upgraded, 0 newly installed, 1 to remove and 112 not upgraded.
After this operation, 11.7 MB disk space will be freed.
Do you want to continue? [Y/n] 
(Reading database ... 144876 files and directories currently installed.)
Removing postgresql-9.3 (9.3.24-0ubuntu0.14.04) ...
 * Stopping PostgreSQL 9.3 database server                                                                 * Error: Config owner (postgres:107) and data owner (root:0) do not match, and config owner is not root
                                                                                                   [fail]
invoke-rc.d: initscript postgresql, action "stop" failed.
dpkg: error processing package postgresql-9.3 (--remove):
 subprocess installed pre-removal script returned error exit status 1
E: Sub-process /usr/bin/dpkg returned an error code (1)
12:51:30::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ cd ../10/
12:51:42::mlissner@new-courtlistener::/var/lib/postgresql/10 
↪ ls
total 4.0K
drwx------ 19 postgres 4.0K Oct 27 23:05 main/
12:51:43::mlissner@new-courtlistener::/var/lib/postgresql/10 
↪ cd ../9.3/
12:51:48::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ sudo chown postgres:postgres main/
12:52:01::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ sudo apt-get remove postgresql-9.3 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages will be REMOVED:
  postgresql-9.3
0 upgraded, 0 newly installed, 1 to remove and 112 not upgraded.
After this operation, 11.7 MB disk space will be freed.
Do you want to continue? [Y/n] 
(Reading database ... 144876 files and directories currently installed.)
Removing postgresql-9.3 (9.3.24-0ubuntu0.14.04) ...
 * Stopping PostgreSQL 9.3 database server                                                         [ OK ] 
Processing triggers for postgresql-common (195.pgdg14.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
12:52:08::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ sudo service postgresql status
9.3/main (port 5433): down
10/main (port 5432): online
12:52:16::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ sudo apt-get purge --dry-run postgresql-9.3 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages will be REMOVED:
  postgresql-9.3*
0 upgraded, 0 newly installed, 1 to remove and 112 not upgraded.
Purg postgresql-9.3
12:52:33::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ dpkg -l | grep postg
ii  pgdg-keyring                          2018.1+b1                                  all          keyring for apt.postgresql.org
ii  postgresql-10                         10.5-2.pgdg14.04+1                         amd64        object-relational SQL database, version 10 server
rc  postgresql-9.3                        9.3.24-0ubuntu0.14.04                      amd64        object-relational SQL database, version 9.3 server
ii  postgresql-client-10                  10.5-2.pgdg14.04+1                         amd64        front-end programs for PostgreSQL 10
ii  postgresql-client-9.3                 9.3.24-0ubuntu0.14.04                      amd64        front-end programs for PostgreSQL 9.3
ii  postgresql-client-common              195.pgdg14.04+1                            all          manager for multiple PostgreSQL client versions
ii  postgresql-common                     195.pgdg14.04+1                            all          PostgreSQL database-cluster manager
ii  postgresql-server-dev-9.3             9.3.24-0ubuntu0.14.04                      amd64        development files for PostgreSQL 9.3 server-side programming
ii  postgresql-server-dev-all             154ubuntu1.1                               all          extension build tool for multiple PostgreSQL versions
12:52:45::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ sudo apt-get remove postgresql-client-9.3 postgresql-server-dev-9.3
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following package was automatically installed and is no longer required:
  dctrl-tools
Use 'apt-get autoremove' to remove it.
The following packages will be REMOVED:
  postgresql-client-9.3 postgresql-server-dev-9.3 postgresql-server-dev-all
0 upgraded, 0 newly installed, 3 to remove and 109 not upgraded.
After this operation, 6,440 kB disk space will be freed.
Do you want to continue? [Y/n] 
(Reading database ... 144772 files and directories currently installed.)
Removing postgresql-client-9.3 (9.3.24-0ubuntu0.14.04) ...
Removing postgresql-server-dev-all (154ubuntu1.1) ...
Removing postgresql-server-dev-9.3 (9.3.24-0ubuntu0.14.04) ...
Processing triggers for postgresql-common (195.pgdg14.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
12:53:31::mlissner@new-courtlistener::/var/lib/postgresql/9.3 
↪ dpkg -l | grep postg
ii  pgdg-keyring                          2018.1+b1                                  all          keyring for apt.postgresql.org
ii  postgresql-10                         10.5-2.pgdg14.04+1                         amd64        object-relational SQL database, version 10 server
rc  postgresql-9.3                        9.3.24-0ubuntu0.14.04                      amd64        object-relational SQL database, version 9.3 server
ii  postgresql-client-10                  10.5-2.pgdg14.04+1                         amd64        front-end programs for PostgreSQL 10
ii  postgresql-client-common              195.pgdg14.04+1                            all          manager for multiple PostgreSQL client versions
ii  postgresql-common                     195.pgdg14.04+1                            all          PostgreSQL database-cluster manager

Note that the removal script provided by postgres previously made it difficult to uninstall the software using ubuntu's tools. Ugh, ugh, ugh.

mlissner commented 5 years ago

This is finally all set.