How to install PostgreSQL from binaries on Mac OS X Catalina
Tags: rails, postgres, database
Published: 2020-03-28 13:40:15

_Evening people._

There are millions ways of installing RDBMS like *PostgreSQL* on your laptop in 2k20. Some of them require just a few hits on your keyboard while others ask you to have solid extent of courage and enthusiasm.

Let's quickly sketch out a list of possible techniques that modern world offers us to make use of PostgreSQL (assuming we're using *Mac OS*):

1. Brew.
2. Docker.
3. EDB Postgres.
4. _Source code._

Describing pros and cons of each of these methods requires separate posts, but if you feel as adventurous as me the day before I'm composing this posts let's get down to business.

The problem is I haven't found any comprehensive enough description of how to go through the process of installation PostgreSQL from the start to the working database, so I'm making my own.

We're gonna install PostgreSQL 11.7, 'cause that was what I needed, but you can try different version with presumably the same degree of success (but I won't promise that 😉 ).

First of all I should say that PostgreSQL has a wonderful and comprehensive [documentation](https://www.postgresql.org/docs/11/index.html) that I used a lot and you can surely help yourself as well.

So let's go step by step (I know everyone likes that) towards fully working PostgreSQL on your machine.

## 1. Downloading

What we need to make a first step is to prosaically download the sources from the  postgresql site. Here is the [link](https://www.postgresql.org/ftp/source/). What you need here is to pick a version you want and download something like `postgresql-<your-version>.tar.gz`. It could be `postgresql-11.7.tar.gz`. I downloaded to `~/Downloads` folder so you can do the same.

## 2. Extracting

Just extract the sources from the archive you've just download by _double-clicking the file_. Alternatively you can choose any option you like for extracting.

## 3. Enter the door

On this step you should have unpacked folder in one of your directories. Again, mine was `~/Downloads`, you can have different. This is the time we need to _open the terminal_ and do some action.

Let's `cd` into the directory you unpacked your copy of the binaries to.

In my case it was

```
cd ~/Downloads/postgresql-11.7
```

Please don't forget _to adjust_ folder and version according you what you've got previously.

You can spend sometime learning what inside of this treasure box and when you're ready to proceed let's to it.

## 4. Configuring

This is the most _interesting_ and _important_ step. On this step we need to configure the process in a way that `make` command will be able to find all required libraries that are already installed on you system to successfully build your fully working PostgreSQL dream.

_lyrical digression_. PostgreSQL offers you multiple variants of installation. And the most basic one doesn't include some important extensions that you will probably need in your software development process. Some of them are: *btree_gin* for maintaining indiсes, *citext* for a case-insensitive string management, *hstore* that helps to store _key-value pairs_ in a single PostgreSQL value. We'll need that info a bit later, but for now just keep that in mind.

I'm not honest with you if I say that there are no `easy` path to install PostgreSQL from the binaries. Here's the (link)[https://www.postgresql.org/docs/11/install-short.html] to what even PostgreSQL offers to you to easily shorten you way. Another thing is that didn't work for me. Or for Mac OS.

So there are few more steps we need to go through before starting to run scripts and build PostgreSQL.

### 4.1 OpenSSL

This was my bottleneck. While configuring there were always something missing. And from reading the logs I understood that it was OpenSSL.

So what you need here is to make sure you have OpenSSL installed on your machine.

There is a simple way to verify that:

```
openssl version
```

Yes, without any dashes.

You would probably get something like `OpenSSL 1.1.1d  10 Sep 2019`. If you have older version I would suggest you to upgrade it.

And here we're gonna leverage the brew helper.

```
brew upgrade openssl
```

And if you're missing openssl at all try to install it:

```
brew install openssl
```

Hope everything is great on the current phase, so now we need to find out where the openssl library installed on your machine. Easy as duck:

```
which openssl
```

You would get something similar to `/usr/local/opt/openssl@1.1/bin/openssl`. You might have slightly different result, but that doesn't matter unless it's says you don't have openssl at all. The folder we're interested in is two steps back. Meaning that in this case you should throw two slashes off and get `/usr/local/opt/openssl@1.1`. Let's remember that as a `openssl_folder` and for now just remember that.

### 4.2 Configure with flags

Now we're ready to start configuring our build process. If you've left your downloaded and unpacked `postgresql-<your-version>` folder, this is the time to get back:

```
cd ~/Downloads/postgresql-11.7
```

Just paste in you path and you're should be good to go.

Our goal is to run `configure` with openssl flag that requires three more flags passed.

- `--with-openssl` itself.
- `--with-includes` that should be equal to our `openssl_folder` with `/include postfix`. So you should get something like `/usr/local/opt/openssl@1.1/include`.
- `--with-libraries` that should be equal to our `openssl_folder` with `lib` postfix. Something similar to `/usr/local/opt/openssl@1.1/lib`.

Let's collect everything together. From inside of your postgresql folder run:

```
./configure --with-openssl --with-includes=/usr/local/opt/openssl@1.1/include --with-libraries=/usr/local/opt/openssl@1.1/lib
```

Be attentive and don't forget to paste paths that _you_ got. They are not necessary equal to those described above.

Now please wait some time and make sure there are no errors that your terminal complains about.

If so, let's get a step further.

## 5. Making

Now we should be good to go with the _making_ our files with the GNU `make` utility. Chances are you already have it.

Remember that explanation about different variants of the PostgreSQL installation. This is the time to leverage that info. So if you need to have everything installed along with docs and extensions, run from the same folder:

```
make world
```
But if you would like to install plain version just skip the `world` and simply run `make`.

Wait a bit again and if there are still no errors let's get to the installation.

## 6. Installation

Based on what you chose the step before you should run

if your choice was `make world`:
```
sudo make install-world
```

if your choice was just `make`:
```
sudo make install
```

It should say that the PostgreSQL is successfully installed. Yay! Half of the way is done if not even more!

## 7. Creating a dedicated user

Now we need to created a separate user in your system for the safety reasons that will be fully responsible for what happening with your PostgreSQL installation.

This can be done in different manners.

Best practice is to create user called `postgres`. Different ways of installation create that for you, but here we need to do that ourselves.

I personally went the UI way and followed this [guide](https://www.imore.com/how-create-new-user-account-your-mac). You need to name your user `postgres` put the password for him.

There are command-line options for creating users like `dscl`, but it didn't work out for me for some reasons.

## 8. Creating the data folder

Now we need to create a data folder that will store all postgresql data.
Chances are you won't have enough permissions without sudo. So run:

```
sudo mkdir /usr/local/pgsql/data
```

Make sure you point that to the correct folder.

_important thing_ if you already have that data folder I would suggest to completely remove it unless it has some important data you don't want to lose.

## 9. Who's in charge?

Although we created previous folder under the root user we need to transfer permission to our newly created `postgres` user. As simply as:

```
chown postgres /usr/local/pgsql/data
```

Viola. Permission granted.

## 10. Log file

Another small thing we would like to do is to create a log file for our PostgreSQL server. Please run this series of commands:

```
sudo mkdir /var/log/postgresql
sudo touch /var/log/postgresql/server.log
sudo chown postgres /var/log/postgresql
```
Let's remember that file as a `server_logfile`.

## 11. Dive into world with the postgres host

It's time to login as a `postgres` user. Just type the following:

```
su - postgres
```

and we're under `postgres` now.

Now it's time to find out what shell is used by your `postgres` user. Type:

`echo $0`

Why we need to know the shell type? Because we need to store some environment variables to simplify our life a bit.

So, based on the result of the previous operation let's create `~/.bashrc`, `/.zshrc` or anything different if you have different output of the `echo` command:

```
touch ~/.zshrc
```
These are some nifty things we would like to add:

1. It's nice to add that path to the `$PGDATA` environment variable that will lead to a database cluster path and will save us some time on avoiding passing `-D` option when we start a database server.

So you can open your shell `rc` file and paste:

```
export PGDATA="/usr/local/pgsql/data"
```

Make sure you correctly point the path we put together in `mkdir` command when we created data folder.

2. Another thing that can ease your life is adding existing postgresql `bin folder` to the `$PATH`.

To do that please open your shell `.rc` file (`~/.bashrc`, `~/.zshrc`) and paste the following:

```
export PATH="$PATH:/usr/local/pgsql/bin"
```

Don't forget to reload your shell afterwards:

```
source ~/.bashrc
```

or any different shell `rc` file that you use.

Adding path to executable saves you time to skip prepending commands like `createuser`, `pg_ctl` and obviously `postgres` with the full path.

## 12. Initializing the data cluster

So the full command requires a apth to the executables and a `-D` flag as a path to the data cluster folder. Since we added the `$PGDATA` environment variable we can skip `-D` option and also we can skip writing full path to the executable, 'cause we added that to the `$PATH`.

So let's do that:

```
initdb
```

If everything is okay let's move forward.

## 13. Starting the server

Starting the server requires a logfile parameter by passing the `-l` flag. Remember that `server_logfile` that we created. We'll use `pg_ctl` utility. So starting the server now as easy as:

```
pg_ctl -l /var/log/postgresql/server.log start
```
As a result you get a brand new postgresql daemon process which pid is located in the `/usr/local/pgsql/data/postmaster.pid` so you can always check that out and kill it if you need.

You can also run that with plain `postgresql` command o start the server:

```
/usr/local/pgsql/bin/postgres > /var/log/postgresql/server.log 2>&1 &
```

That appendix in the end is needed for running the server in the background.

## 14. Creating first database

Congrats! Now you have a working PostgreSQL server.

You can try to create a new database:

```
createdb test
```

And connect to it:

```
psql test
```

So, basically that's it! Remember that you should do everything under `postgres` user and don't forget to log out from it when you're done.

## Bonus: Rails setup

### database.yml

I faced a few issues when connecting to the database server created this way. So, you should be very attentive in constructing your `config/database.yml` file. The most important thing here is to put `host: localhost` pair and don't forget to insert correct username and password. So your `config/database.yml` should look approx like this:

```
default: &default
 adapter: postgresql
 encoding: unicode
 username: postgres
 password: <your-password>
 host: localhost
 pool: 5

development:
 <<: *default
 database: something_development
```

Then run:

```
rails db:create
```

And if you see the encoding error I invite you to the next section.

### Endcoding

By default template tables in the PostgreSQL have `SQL_ASCII` encoding where you might need `unicode`. So, here's how to fix that.

Firstly run `psql` under `postgres` user so you can influence your databases and run these series of commands to adjust template tables encoding:

```
UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
DROP DATABASE template1;
CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
\c template1
VACUUM FREEZE;
```

Essentially this is the excerpt from the stackoverflow (answer)[https://stackoverflow.com/a/16737776].

That's it.

## Epilogue

You may ask why you need to do all that stuff instead of making use of the Docker and do most of that in a minute? I don't know. Probably you just enjoy the process.

At last I would like to say that putting together a perfect PostgreSQL installation guide is definitely not the goal. I just want to help people like me to overcome some issues while building PostgreSQL from the source code. Please feel free to leave comments and... hope you enjoyed!

🙏

References:

https://www.postgresql.org/docs/11/index.html
https://medium.com/@kevinmircovich/pg-connectionbad-could-not-connect-to-server-no-such-file-or-directory-da18a3764a0a
https://stackoverflow.com/a/16737776
https://www.imore.com/how-create-new-user-account-your-mac
https://labs.wordtothewise.com/postgresql-osx/