Reinstall PostgreSQL with OpenSSL using asdf
Tags: postgres
Published: 2021-02-27 22:04:28

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`](https://github.com/asdf-vm/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.

1. Create a dump of all the existing 13.1 databases I have. I don't want to lose this local data.
2. Uninstall Postgres 13.1
3. Install Postgres 13.1 with OpenSSL using the [asdf-postgres](https://github.com/smashedtoatoms/asdf-postgres) `POSTGRES_EXTRA_CONFIGURE_OPTIONS` flag.
4. 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`](https://www.postgresql.org/docs/current/app-pg-dumpall.html). 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](https://github.com/smashedtoatoms/asdf-postgres#asdf-options), 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. 🎉

---

<em><span>Cover photo by <a href="https://unsplash.com/@marekpiwnicki?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Marek Piwnicki</a> on <a href="https://unsplash.com/t/wallpapers?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></span></em>