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):
- EDB Postgres.
- 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.
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.
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.
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.
cd into the directory you unpacked your copy of the binaries to.
In my case it was
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.
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.
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:
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:
You would get something similar to
/firstname.lastname@example.org/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
/email@example.com. Let's remember that as a
openssl_folder and for now just remember that.
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:
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-includesthat should be equal to our
/include postfix. So you should get something like
--with-librariesthat should be equal to our
libpostfix. Something similar to
Let's collect everything together. From inside of your postgresql folder run:
./configure --with-openssl --firstname.lastname@example.org/include --email@example.com/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.
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:
But if you would like to install plain version just skip the
world and simply run
Wait a bit again and if there are still no errors let's get to the installation.
Based on what you chose the step before you should run
if your choice was
sudo make install-world
if your choice was just
sudo make install
It should say that the PostgreSQL is successfully installed. Yay! Half of the way is done if not even more!
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.
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.
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.
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
It's time to login as a
postgres user. Just type the following:
su - postgres
and we're under
Now it's time to find out what shell is used by your
postgres user. Type:
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
/.zshrc or anything different if you have different output of the
These are some nifty things we would like to add:
- It's nice to add that path to the
$PGDATAenvironment variable that will lead to a database cluster path and will save us some time on avoiding passing
-Doption when we start a database server.
So you can open your shell
rc file and paste:
Make sure you correctly point the path we put together in
mkdir command when we created data folder.
- Another thing that can ease your life is adding existing postgresql
bin folderto the
To do that please open your shell
.rc file (
~/.zshrc) and paste the following:
Don't forget to reload your shell afterwards:
or any different shell
rc file that you use.
Adding path to executable saves you time to skip prepending commands like
pg_ctl and obviously
postgres with the full path.
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
So let's do that:
If everything is okay let's move forward.
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.
Congrats! Now you have a working PostgreSQL server.
You can try to create a new database:
And connect to it:
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.
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
And if you see the encoding error I invite you to the next section.
By default template tables in the PostgreSQL have
SQL_ASCII encoding where you might need
unicode. So, here's how to fix that.
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].
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!