Postgres + Goodies

Unmodified Postgres with some useful plugins. Our goal is not to modify Postgres, but to provide some of the most common extensions with a one-click install. Find out more in our github repository.

Features

• ✅ Postgres 12. Includes generated columns and JSON path support.
• ✅ Ubuntu 18.04 (Bionic).
• ✅ pg-contrib-12. Because everyone should enable pg_stat_statements.
• ✅ wal_level = logical and max_replication_slots = 5. Ready for replication.
• ✅ PostGIS. Postgres' most popular extension - support for geographic objects.
• ✅ pgTAP. Unit Testing for Postgres.
• ✅ pgAudit. Generate highly compliant audit logs.
• ✅ pgjwt. Generate JSON Web Tokens (JWT) in Postgres.
• ✅ pgsql-http. HTTP client for Postgres.
• ✅ plpgsql_check. Linter tool for PL/pgSQL.
• ✅ plv8. Write in Javascript functions in Postgres.
• ✅ PL/Java. Write in Java functions in Postgres.
• ✅ pg_cron. Run CRON jobs inside Postgres.
• ✅ pg-safeupdate. Protect your data from accidental updates or deletes.
• ✅ PgBouncer. Connection Pooling for PostgreSQL.

General Instructions

In addition to the package installation, this droplet also enables the UFW firewall to allow port 22 for SSH and port 5432 for access to PostgreSQL. Once you have your droplet up and running, you would first need to set up the password for your DB superuser postgres.

Once you have SSH into your instance, type in the following:

$ sudo -u postgres psql postgres

Then, in the psql terminal, type in the following:

$ \password postgres

Enter your desired password and reconfirm. You can disconnect from your SSH afterwards. Now, with any SQL client of your choice, you can now connect to the database with the following credentials:

Host: insert droplet ip address

Port: 5432

User: postgres

Password: insert your chosen password

Database: postgres

At any point wherein you would want to check whether your database is up, you can type this into your host terminal:

$ pg_isready -h <insert droplet ip address> -p 5432

Using Attached Volumes

If you are using attached volumes along with your droplet, you can use a volume as the default data directory for your Postgres server. A comprehensive guide on how to move your data directory to any such volume can be found here.