Overview:
AWS RDS and Azure Database for PostgreSQL are excellent offerings, but can quickly escalate in price as databases grow. Hosting Postgres on VMs takes effort, but may be a valuable cost-saving alternative with greater flexibility to customize the database.
These steps generally apply to any Server or VM, but are specifically focused on deployment to Azure VMs using Postgres 12.1 on Ubuntu 18.
Steps:
- Create a VM with SSH access from your IP address
- Configure and Mount your data drives. See Azure Documentation.
- Verify the drives and configured properly
df4. Update and Upgrade the OS
sudo apt-get -y upgrade
sudo apt-get -y update
sudo apt-get -y --with-new-pkgs upgrade
sudo apt-get -y autoremove5. Install PostgreSQL 12.xx
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main 12" > /etc/apt/sources.list.d/PostgreSQL.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get -y update
sudo apt-get -y install postgresql-126. Grant the Postgres ownership of the Postgres directory
sudo chown postgres -R /var/run/postgresql
7. Create the directory for the database
sudo mkdir /datadrive/postgres
sudo mkdir /datadrive/postgres/data
sudo chown postgres -R /datadrive/postgres8. Initialize the database in the data directory
cd /usr/lib/postgresql/12/bin
sudo -u postgres ./initdb -D /datadrive/postgres/data9. Edit the Postgres configuration file
sudo -u postgres nano /datadrive/postgres/data/postgresql.conf
Typically, changes are made at the end of the file. If any values are duplicated, the last occurrence in the file will be used.
Possible configuration settings:
listen_addresses = '*'
port = 5781
max_connections = 100
superuser_reserved_connections = 3
tcp_keepalives_idle = 60
tcp_keepalives_interval = 60
tcp_keepalives_count = 10
password_encryption = scram-sha-256
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 3d
log_rotation_size = 30MB
log_min_messages = warning
log_min_error_statement = errorNote: I prefer to use an atypical port for databases, perhaps making it slightly more secure. In this case, the port is set at 5781, but could be set to whatever. The default Postgres port is 5432.
10. Install AzCopy
AzCopy is a nice tool for copying files to and from VMs, including backup files
sudo mkdir /datadrive/azcopy
cd /datadrive/azcopy
sudo wget -O azcopy.tar.gz https://aka.ms/downloadazcopy-v10-linux
sudo tar -xf azcopy.tar.gz
sudo rm azcopy.tar.gz
sudo cp azcopy_linux_amd64*/* .
sudo rm azcopy_linux_amd64*/*
sudo rmdir azcopy_linux_amd64*11. Install the SSL Certificate
Copy your SSL Certificate files to /datadrive/postgres/data/server.crt and /datadrive/postgres/data/server.key. You could download these from your Azure Storage Account using AzCopy or copy them to the server using another mechanism.
sudo ./azcopy copy "{Blob URL including SAS}" /datadrive/postgres/data/server.crt
sudo ./azcopy copy "{Blob URL including SAS}" /datadrive/postgres/data/server.key
12. Grant the Postgres user ownership of the SSL certificate and set the appropriate permissions on the key.
sudo chown postgres /datadrive/postgres/data/server.crt
sudo chown postgres /datadrive/postgres/data/server.key
sudo chmod og-rwx /datadrive/postgres/data/server.key13. Grant external access to the database
sudo -u postgres nano /datadrive/postgres/data/pg_hba.conf
Content to add at the bottom of the pg_hba.conf file:
hostssl all all 0.0.0.0/0 scram-sha-256
hostssl all all ::/0 scram-sha-256
hostnossl all all 0.0.0.0/0 reject
hostnossl all all ::/0 rejectThis only allows access via SSL and requires scram-sha-256 password hashing (the current more-secure option).
14. Start the database
cd /usr/lib/postgresql/12/bin
sudo -u postgres ./pg_ctl -D /datadrive/postgres/data start15. Verify that the database is listening on the correct port and host
netstat -nlt16. Connect to Postgres
sudo -u postgres psql -p 5781Once connected, create an initial database and admin user
CREATE DATABASE {DB NAME};
CREATE USER dbsuper WITH ENCRYPTED PASSWORD '{DB PASSWORD}' CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE {DB NAME} to dbsuper;
ALTER USER dbsuper WITH SUPERUSER;
\q17. Configure Postgres to start automatically
sudo nano /lib/systemd/system/postgresql.service
Content:
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
[Service]
Type=notify
User=postgres
ExecStart=/usr/lib/postgresql/12/bin/postgres -D /datadrive/postgres/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.targetUpdate systemd script permissions:
sudo chmod 644 /lib/systemd/system/postgresql.service
Stop and Reload using postgresql.service:
sudo -u postgres ./pg_ctl -D /datadrive/postgres/data stop
sudo systemctl daemon-reload
sudo systemctl stop postgresql.service
sudo systemctl start postgresql.service18. Verify the status of the service:
sudo systemctl status postgresql.service19. Reboot and Verify that the database is running
sudo reboot
netstat -nlt20. Configure backups
PostgreSQL backups can be done using the pg_dump utility installed with the database. These will provide a consistent backup as of a moment in time. AzCopy could be used to archive these backups to Azure Storage Blobs.
Additionally, copying the Write Ahead Logs (WAL) to a warm standby using a utility like rsync can give you an immediate failover in the event to failure.
Congratulations! You just completed an involved process. Perhaps it's time to look at AWS RDS or Azure Database for PostgreSQL again now that you know what it takes to do it yourself!
In any case, thank you for reading. I'd appreciate your thoughts or questions.