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):
- Brew.
- Docker.
- 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.
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 ouropenssl_folder
with/include postfix
. So you should get something like/usr/local/opt/openssl@1.1/include
. -
--with-libraries
that should be equal to ouropenssl_folder
withlib
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:
- 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.
- 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/
Top comments (3)
Thank you so much for sharing. This article just saved my life. I needed to work with an obscure db version that didn't have an easy installer. This article did the trick.
If you need uuid support on Mac, you need to build with e2fs
brew install e2fsprogs
./configure --with-openssl --with-includes=/usr/local/opt/openssl@1.1/include --with-libraries=/usr/local/opt/openssl@1.1/lib --with-uuid=e2fs
Guys, it's not worth it. Use
brew
please.well, i landed here because the brew alternative doesnt do it for me. sure i get postgres installed and all, but when trying to install psycopg2-binary inside a pycharm environment for django i get a massive error which i am not able to solve. so just saying 'use brew' doesnt help much either