I spun up a render.com PostgreSQL instance. They gave my a psql
connection string, so I copied that into my terminal and hit enter. I half expected it to Just Work™. To Render's credit, everything on their end went smoothly. The issue was with my PostgreSQL psql
client.
$ psql postgres://username:password@oregon-postgres.render.com/dbname
psql: error: FATAL: SSL/TLS required
Hmm 🤔 I guess I need to tell psql
to require SSL for this connection by adding sslmode=require
.
$ psql postgres://username:password@oregon-postgres.render.com/dbname?sslmode=require
psql: error: sslmode value "require" invalid when SSL support is not compiled in
That gives me an enlightening and more actionable error message. The psql
client that I have installed on my machine was not compiled/built with SSL support (i.e. OpenSSL).
I used asdf
to install Postgres, which comes with the psql
client. I don't remember having SSL support as an option when I installed it. Though I would have expected it to have been a default.
I can check how this version of Postgres was installed—based on the configuration flags that were used—using the pg_config
command.
First though, let's set a local variable for the prefix location for many of these commands.
$ local ASDF_PG_PREFIX=$(asdf where postgres 13.1)
$ echo $ASDF_PG_PREFIX
/Users/jbranchaud/.asdf/installs/postgres/13.1
Now I can run pg_config
from that directory's bin
with the --configure
flag.
$ $ASDF_PG_PREFIX/bin/pg_config --configure
'--prefix=/Users/jbranchaud/.asdf/installs/postgres/13.1'
The only configuration flag that asdf
used when I installed Postgres 13.1 was the --prefix
flag. It has been a few months, but I assume I ran asdf install postgres 13.1
with no other options.
So, what can I do about this?
I can reinstall Postgres 13.1 with OpenSSL support and then I should be able to connect with sslmode=require
to that remote database.
This is going to involve a few steps.
- Create a dump of all the existing 13.1 databases I have. I don't want to lose this local data.
- Uninstall Postgres 13.1
- Install Postgres 13.1 with OpenSSL using the asdf-postgres
POSTGRES_EXTRA_CONFIGURE_OPTIONS
flag. - Restore my 13.1 databases using
pg_restore
with the dump I created in Step 1.
Dumping All That Data
Another one of the handy executables that comes with a Postgres install is pg_dumpall
. This command will dump all schema and data for each database to stdout
as SQL
statements. This can then be redirected to a .sql
file.
$ pg_dumpall \
--exclude-database="template*" \
> reinstall_13_1_dump.sql
I included the --exclude-database
flag with the template*
pattern to excluding dumping (and eventually restoring) the template0
and template1
databases. These will be included with a fresh install of Postgres, so there is no need to carry them around in a dump file.
Uninstall PostgreSQL
I first stop the Postgres server.
$ $ASDF_PG_PREFIX/bin/pg_ctl -D $ASDF_PG_PREFIX/data stop
server stopped
Then I can tell asdf
to uninstall the version I'll be reinstalling.
$ asdf uninstall postgres 13.1
Install PostgreSQL with OpenSSL
The asdf-postgres
plugin explains that extra configuration options can be included with this option:
POSTGRES_EXTRA_CONFIGURE_OPTIONS
- append these configure options along with ones that this plugin already uses
Following after their example, I use the --with-openssl
flag and then specify my libraries and includes, based on where my openssl is installed.
$ POSTGRES_EXTRA_CONFIGURE_OPTIONS="--with-openssl --with-libraries=/usr/local/lib:/usr/local/opt/openssl/lib --with-includes=/usr/local/include:/usr/local/opt/openssl/include" \
asdf install postgres 13.1
...
Success. You can now start the database server using:
...
Success! This likely took a while to compile and longer than it would have without the OpenSSL support. Good job hanging in there.
I can now tell asdf
I want to use the newly installed version of Postgres, start the server up, and try connecting:
$ asdf local postgres 13.1
$ $ASDF_PG_PREFIX/bin/pg_ctl -D $ASDF_PG_PREFIX/data start
server started
$ psql
2021-02-27 17:00:35.118 CST [72552] FATAL: database "jbranchaud" does not exist
psql: error: FATAL: database "jbranchaud" does not exist
That last error is a good sign. It means the default database named for my unix user hasn't been created yet. That's the sign of a freshly installed Postgres instance. That will take us to the next step of restoring the dumped data.
But first, just real quick, let's check how this Postgres instance has been configured.
$ $ASDF_PG_PREFIX/bin/pg_config --configure
'--prefix=/Users/jbranchaud/.asdf/installs/postgres/13.1' '--with-openssl' '--with-libraries=/usr/local/lib:/usr/local/opt/openssl/lib' '--with-includes=/usr/local/include:/usr/local/opt/openssl/include'
Yay, the flags we specified were used. Moving on.
Restoring My Dumped Databases
I can restore my dumped database with psql
command pointed at that file.
$ psql -f reinstall_13_1_dump.sql postgres
The last argument (postgres
) is the database that the psql
command is running against. Without specifying that, it was trying to preemptively connect to the not-yet-existing jbranchaud
database before processing the lines of SQL in the dump file.
Check that all the database were restored as expected:
$ psql -c '\l'
This sends the \l
command to psql
which will list out all the database in this cluster.
I can double check that SSL is enabled from psql
:
show ssl;
ssl
-----
on
It is. And I am now able to connect to that SSL-required Render database. 🎉
Cover photo by Marek Piwnicki on Unsplash
Top comments (1)
I proactively used the flags detailed in this post to install the SSL version of PostgreSQL 13.4.