PostgreSQL - HVboom/HowTo-DigitalOcean GitHub Wiki

PostgreSQL is a sophisticated Object-Relational DBMS, supporting almost all SQL constructs, including subselects, transactions, and user-defined types and functions. It is the most advanced open-source database available anywhere. Commercial Support is also available.

The original Postgres code was the effort of many graduate students, undergraduate students, and staff programmers working under the direction of Professor Michael Stonebraker at the University of California, Berkeley. In 1995, Andrew Yu and Jolly Chen took on the task of converting the DBMS query language to SQL and created a new database system which came to known as Postgres95. Many others contributed to the porting, testing, debugging and enhancement of the Postgres95 code. As the code improved, and 1995 faded into memory, PostgreSQL was born.

PostgreSQL development is presently being performed by a team of Internet developers who are now responsible for all current and future development. The development team coordinator is Marc G. Fournier ([email protected]). Support is available from the PostgreSQL developer/user community through the support mailing list ([email protected]).

PostgreSQL is free and the complete source is available.

💬 copied from package description

• PostgreSQL.org

Latest PostgreSQL versions can automatically installed with the PostgreSQL Apt Repository

• Install as APT package

  sudo apt install -y postgresql-common
  sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
  
  ...
  
  You can now start installing packages from apt.postgresql.org.
  
  Have a look at https://wiki.postgresql.org/wiki/Apt for more information;
  most notably the FAQ at https://wiki.postgresql.org/wiki/Apt/FAQ

• Install latest PostgreSQL version

  sudo apt update && sudo apt upgrade
  sudo apt install postgresql
  sudo apt install libpq-dev

• Security check

  # login with the PostgreSQL superuser
  sudo -iu postgres psql -U postgres
  
  # check encryption settings
  #  should return scram-sha-256
  SHOW password_encryption;
  #  should return 4096
  SHOW scram_iterations;
  #  should return localhost
  SHOW listen_addresses;
  
  # set an encrypted password for the postgres user
  ALTER ROLE postgres WITH ENCRYPTED PASSWORD '<password>';
  # quit postgres
  \q
  
  # change the access method for the postgres user from peer to scram-sha-256
  cd /etc/postgresql/16/main
  sudo cp -p pg_hba.conf pg_hba.conf.ORIGIN
  sudo vi pg_hba.conf
  sudo systemctl reload postgresql

  • Content of /etc/postgresql/16/main/pg_hba.conf

    # Database administrative login by Unix domain socket
    local   all             postgres                                scram-sha-256
    
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    
    # "local" is for Unix domain socket connections only
    local   all             /^hv                                    scram-sha-256
    local   sameuser        all                                     peer 
    local   all             all                                     reject
    
    # IPv4 local connections:
    # host    all             all             127.0.0.1/32            scram-sha-256
    
    # IPv6 local connections:
    hostssl    all           postgres       ::1/128                 scram-sha-256

• Enable SSL connections with Let's Encrypt

  • Open the Firewall to allow external connections to the PostgreSQL service: sudo ufw allow 5432

  • Create a 'Let's Encrypt' deploy script /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy to automatically copy new certificates

    #!/bin/env bash
    DOMAIN=hvboom.biz
    SSL_DIR=/var/lib/postgresql/.ssl
    
    umask 0077
    mkdir -p $SSL_DIR
    
    umask 0177
    cp /etc/letsencrypt/live/$DOMAIN/fullchain.pem $SSL_DIR
    cp /etc/letsencrypt/live/$DOMAIN/privkey.pem $SSL_DIR
    
    chown -R postgres:postgres $SSL_DIR

  • Update SSL section in the configuration file /etc/postgresql/16/main/postgresql.conf

    ...
    
    # - SSL -
    
    ssl = on
    #ssl_ca_file = ''
    #ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
    ssl_cert_file = '/var/lib/postgresql/.ssl/fullchain.pem'
    #ssl_crl_file = ''
    #ssl_crl_dir = ''
    #ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
    ssl_key_file = '/var/lib/postgresql/.ssl/privkey.pem'
    #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
    ssl_ciphers = 'ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM'
    #ssl_prefer_server_ciphers = on
    ssl_prefer_server_ciphers = on
    #ssl_ecdh_curve = 'prime256v1'
    #ssl_min_protocol_version = 'TLSv1.2'
    #ssl_max_protocol_version = ''
    #ssl_dh_params_file = ''
    #ssl_passphrase_command = ''
    #ssl_passphrase_command_supports_reload = off
    
    ...
    

  • Test connection

    psql -h ::1 -U postgres
    Password for user postgres: 
    psql (16.4 (Ubuntu 16.4-1.pgdg24.04+1))
    SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
    Type "help" for help.
    
    postgres=#

• Install as normal package

  sudo pkg install postgresql11-server postgresql11-contrib
  
  ....
  
  [1/4] Installing postgresql11-server-11.5_1...
  ===> Creating groups.
  Creating group 'postgres' with gid '770'.
  ===> Creating users
  Creating user 'postgres' with uid '770'.
  
    =========== BACKUP YOUR DATA! =============
    As always, backup your data before
    upgrading. If the upgrade leads to a higher
    minor revision (e.g. 8.3.x -> 8.4), a dump
    and restore of all databases is
    required. This is *NOT* done by the port!
    ===========================================
  [2/4] Extracting postgresql11-server-11.5_1: 100%
  [3/4] Installing postgresql11-contrib-11.5...
  [4/4] Extracting postgresql11-contrib-11.5: 100%
  =====
  Message from postgresql11-server-11.5_1:
  
  --
  For procedural languages and postgresql functions, please note that
  you might have to update them when updating the server.
  
  If you have many tables and many clients running, consider raising
  kern.maxfiles using sysctl(8), or reconfigure your kernel
  appropriately.
  
  The port is set up to use autovacuum for new databases, but you might
  also want to vacuum and perhaps backup your database regularly. There
  is a periodic script, /usr/local/etc/periodic/daily/502.pgsql, that
  you may find useful. You can use it to backup and perform vacuum on all
  databases nightly. Per default, it performs `vacuum analyze'. See the
  script for instructions. For autovacuum settings, please review
  ~pgsql/data/postgresql.conf.
  
  If you plan to access your PostgreSQL server using ODBC, please
  consider running the SQL script /usr/local/share/postgresql/odbc.sql
  to get the functions required for ODBC compliance.
  
  Please note that if you use the rc script,
  /usr/local/etc/rc.d/postgresql, to initialize the database, unicode
  (UTF-8) will be used to store character data by default.  Set
  postgresql_initdb_flags or use login.conf settings described below to
  alter this behaviour. See the start rc script for more info.
  
  To set limits, environment stuff like locale and collation and other
  things, you can set up a class in /etc/login.conf before initializing
  the database. Add something similar to this to /etc/login.conf:
  ---
  postgres:\
  	:lang=en_US.UTF-8:\
  	:setenv=LC_COLLATE=C:\
  	:tc=default:
  ---
  and run `cap_mkdb /etc/login.conf'.
  Then add 'postgresql_class="postgres"' to /etc/rc.conf.
  
  ======================================================================
  
  To initialize the database, run
  
    /usr/local/etc/rc.d/postgresql initdb
  
  You can then start PostgreSQL by running:
  
    /usr/local/etc/rc.d/postgresql start
  
  For postmaster settings, see ~pgsql/data/postgresql.conf
  
  NB. FreeBSD's PostgreSQL port logs to syslog by default
      See ~pgsql/data/postgresql.conf for more info
  
  NB. If you're not using a checksumming filesystem like ZFS, you might
      wish to enable data checksumming. It can only be enabled during
      the initdb phase, by adding the "--data-checksums" flag to
      the postgres_initdb_flags rcvar.  Check the initdb(1) manpage
      for more info and make sure you understand the performance
      implications.
  
  ======================================================================
  
  To run PostgreSQL at startup, add
  'postgresql_enable="YES"' to /etc/rc.conf
  =====
  Message from postgresql11-contrib-11.5:
  
  --
  The PostgreSQL contrib utilities have been installed. Please see
  /usr/local/share/doc/postgresql/contrib/README
  for more information.

• Initialize

  • Setting UTF8 as system default - see FreeBSD UTF8
  • Make the described changes to /etc/login.conf and /etc/rc.conf
  • Change the default shell to /usr/local/bin/bash for the newly created postgresql user: sudo vipw -d /etc
  • Initialize DB: sudo service postgresql initdb and sudo service postgresql start

psql -U postgres

listen_addresses = 'localhost';

\q

Set a password for the DB superuser

psql -U postgres

ALTER SYSTEM password_encryption = 'scram-sha-256';
ALTER SYSTEM scram_iterations = '4096';

ALTER USER postgres WITH PASSWORD '<secure password>';

\q

Edit configuration /var/db/postgres/data16/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                scram-sha-256
local   all             /^hv                                    scram-sha-256
local   sameuser        all                                     peer
local   all             all                                     reject

# IPv4 local connections - DISABLED:
# host    all             all             127.0.0.1/32            trust

# IPv6 local connections:
host    all             postgres        ::1/128                 scram-sha-256

Restart the DB service sudo service postgresql restart.

• Backup old data bases

  • List all existing data base psql --username=postgres -l
  • For each data base run: pg_dump --create --encoding=UTF8 --column-inserts --format=p --inserts --dbname=gitlab --username=git --file=postgresql_backup/gitlab_git_20191031.sql

• Upgrade to new PostgreSQL version sudo pkg install postgresql16-server postgresql16-contrib

• Import saved data bases

  • For each data base run: psql -U postgres -c "DROP DATABASE gitlab;"
  • For each export file run: psql -U postgres -f postgresql_backup/gitlab_git_20191031.sql | tee postgresql_backup/gitlab_import.log

• Download and install the OSX package
• Create a new Server connection using a SSH tunnel to connect from localhost to the database