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 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. 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. 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.

1. 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/