The below covers typical first-install information which might be relevant in all circumstances, however in most types of server installation there should also be more specific procedural information in The [PostgresInstallation#Othertypesofinstallation "other types"] section.


New PostgreSQL infdb servers should be configured with the addition of the <dice/options/hypatia-db-server.h> header. The goal is to have this header represent everything required for a bare-metal reload with the exception of the data itself. Locale support is now disabled in the header (see postgresql.initargs, to improve performance and restore default sort order. In the meantime some manual installation/reload steps below ...

The following is the list of required steps to initialise a new PostgreSQL database cluster (presuming that the component has already initialised an empty database in {{{/disk/pgdata/psql/}}} for you):

   (note: these steps not required in practice)
         $ #om postgresql stop
         # #rm -r /disk/pgdata/psql/*
 postgres$ #initdb --pgdata=/disk/pgdata/psql --locale=C --encoding=UTF8
         $ #om postgresql configure
         $ #om postgresql start

If reloading from scratch (i.e. when the original PostgreSQL data location has been lost) simply follow standard PostgreSQL procedures, i.e. following initialisation above run psql as postgres using the uncompressed backup file as source. Backups will be in /disk/backups on the server, but if those have also been lost then they will be on the corresponding rsync mirror server and on tape backup.

See the sections below for full explanations of additional steps that should not be necessary but are included for reference.

Disable Locales

The locale and encoding settings are now part of the component's initialisation procedure (see {{{postgresql.initargs}}}) and can be checked with the following PostgreSQL settings:

> select name, setting, short_desc from pg_settings where setting in ('C', 'UTF8');
      name       | setting |                      short_desc
 client_encoding | UTF8    | Sets the client's character set encoding.
 lc_collate      | C       | Shows the collation order locale.
 lc_ctype        | C       | Shows the character classification and case conversion locale.
 lc_monetary     | C       | Sets the locale for formatting monetary amounts.
 lc_numeric      | C       | Sets the locale for formatting numbers.
 lc_time         | C       | Sets the locale for formatting date and time values.
 server_encoding | UTF8    | Sets the server (database) character set encoding.

of these, lc_collate, lc_ctype and server_encoding are most significant.

Extensions / Contrib

Since PostgreSQL 9.1, the contrib modules required by infdb can be installed as extensions. Although full backups of the database should contain the SQL required to load these, until pgluser has been configured to maintain these automatically it is worth noting the commands required:


these may be executed at any time once the infdb database has been created on the server.

PostgreSQL tweaking

This is too complex and mysterious an art to discuss here, but these are the provisional modifications made to the primary infdb server (circa 2010) profile. Be sure to make any such modifications to the machine profile, not the header (or else place them in an #ifdef by machine class).

!postgresql.options mADD(maintenance_work_mem effective_cache_size work_mem shared_buffers wal_buffers)
postgresql.option_maintenance_work_mem               '128MB'
postgresql.option_effective_cache_size               '5GB'
postgresql.option_work_mem                       '48MB'
postgresql.option_wal_buffers                       '8MB'
!postgresql.option_shared_buffers               mSET('1920MB')
!postgresql.option_max_connections               mSET(50) /* excludes administrative connections */

pgluser sync

Note the 'init' flag to pgluser, which is required to overcome the zero-length user-list guard.

EUGEX Feed Login

An http login is required for downloading the EUGEX feeds. To do this the following steps must be carried out manually after installation of the server.

postgres$: cd
postgres$: pwd
postgres$: cat >.wgetrc <<-EOF
http_user = postgres
http_password = PASSWORD

Replacing PASSWORD with the real password.

Other types of installation

With reference to the above, certain types of installation require specific or additional procedures:

Development Server(s)

Nightly dump / reload sync

Taking a copy of the server should be straightforward enough; either follow the steps above, duplicating headers, to create an infdb-compatible server loading data from the 'real' infdb server.

A simple way to set up an nightly refresh from infdb to development infdb is to allow postgres to connect to the development database and schedule the following as postgres or similar:

On the '''live infdb''' server
cron.additions      mADD(pushtodev)
cron.add_pushtodev  30 03 * * * zcat /disk/backup/Hourly-0200.sql.gz | ssh devinfdb psql

if the backup does not include DROP statements it might be necessary to configure a nightly DROP or to trigger a DROP in some way on the development server. It is probably wise to avoid adding dropdb statements to the live server configuration to avoid accidents(!)

On the '''development''' server
auth.users         mADD(postgres)
cron.additions     mADD(dropdev)
cron.add_dropdev   15 03 * * * dropdb tecdb; dropdb infdb

Moving the Main infdb server

If possible, use seamless transfer by dirty/clean rsync and readonly method as described in the "PostgreSQL Version Upgrades" section below. Once the data has moved:

  • #define INFORMATICS_DATABASE_SERVER to enable new functionality
  • #undef INFORMATICS_DATABASE_SERVER on the old server
  • Restart the '''new''' server to allow new components to settle, or:
    • om updaterpms run
    • om file configure
    • om mail stop; /etc/init.d/postfix start; om mail start
  • Update DNS mappings for infdb.
    • Then force DNS update on mail; portal.theon; ui.theon; cvs
  • Copy old data from:
    • /disk/backup
    • /var/lib/pgsql including .ssh/known_hosts
    • /disk/backup/pglogs
    • /disk/data/ (but NOT main database directory)
    • /disk/scratch (if relevant)
  • Generate '''new''' SSH key if this is sensible:
    • push to postgres@cvs:.ssh/authorized_keys

PostgreSQL Version Upgrades

The basic procedure should follow the Upgrading a DICE PostgreSQL Server guide for LCFG hosts. Obviously the changes between versions can be all the more significant where they affect procedural languages or extensions. Additionally, it should be noted that new functionality might require an upgrade to the python psycopg library as this is used by many parts of our system, including the TheonUI and pgluser.

Replication Slave Servers

See PostgresReplication#Addingremovingaslave

-- TimColles - 27 Nov 2018

Topic revision: r3 - 07 Jan 2020 - 16:17:44 - TimColles
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
This Wiki uses Cookies