PostgreSQL installation tutorial

installing_postgresql.md

Let's install PostgreSQL onto your operating system.

As an open source object-relational database management system, PostgreSQL available for MacOS, Linux, and Windows.

Goal for each Operating System

The goal will be to run the following command successfully from the command line (regardless of the OS):

psql -U postgres

This should open the psql interactive shell and print a prompt that looks like:

postgres=#

Installation per OS:

MacOS:

Let's walk through installing PostgreSQL with the postgresapp on Mac.

1. Visit http://postgresapp.com/
2. Download the most recent version --> Click "Download"
3. Open the application, and click "initialize" to create a new PostgreSQL server
4. Ensure that the Postgres.app bin folder has been added to your $PATH; 4.1) In the command line, enter: echo "$PATH" 4.2) Search through the output and make sure Postgres.app/Contents/Version/latest/bin is there in order to ensure that this directory's executables are callable from any directory in bash.
5. In the command line, enter: lsof -i tcp:5432, and ensure that the postgres COMMAND appears. This checks if the Postgres server is now running on port 5432 under the name localhost:postgresql

Linux:

1. Acquire the source code: wget ftp://ftp.postgresql.org/pub/source/v9.3.2/postgresql-9.3.2.tar.bz2
2. Install the packages needed for building Postgres: sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev

Windows:

1. Download the installer specified by EnterpriseDB for all supported PostgreSQL versions. The installer is available here: https://www.postgresql.org/download/windows/

Windows supplement

It is not recomended to install in the default location which is ProgramFiles since this will place your database files in the Progams directory as well.

If the installer fails durring the C++ 2013 runtime install, you probably already have a version of C++ 2013.
You can solve this issue by openening a cmd window in your installer directory and enter:
postgresql-10.4-1-windows-x64.exe --install_runtimes 0

Be sure to select a directory other than ProgramFiles.
The installer should by default install PostgreSQL as a service and start the service.

In order to start and stop the service you will need to be running a cmd window as administrator or open the Windows Services dialog.

You can use the following cmd commands to start and stop the PostgreSQL service.
This assumes your service was named "postgresql-x64-10"
The Windows Services dialog will have the exact name of your installed service if you need to confirm this.

net start postgresql-x64-10
net stop postgresql-x64-10

In order to use the documented pg_ctl command, you will need to set the following environmental variables in your system.
The following assumes you installed PostgreSQL in C:\PostgreSQL

Add to System variables Path:
C:\PostgreSQL\10\bin

Create the following new System variables

PGDATA
C:\PostgreSQL\10\data

PGDATABASE
postgres

PGUSER
postgres

PGPORT
5432

PGLOCALEDIR
C:\PostgreSQL\10\share\locale

If your Windows PostgreSQL service is still running, you should now be able to control the server via the documented pg_ctl commands documented here

If you open a new cmd window and type pg_ctl status you should see something like

pg_ctl: server is running (PID: 9344)
C:/PostgreSQL/10/bin/postgres.exe "-D" "C:\PostgreSQL\10\data

Be aware that these commands override your windows service commands.
This means that if you stop and start the server via the pg_ctl commands it will then be running under the cmd and not the service, therefore; if you close the current cmd window it will shut down your server.

@gregbown Thanks for the Windows guide!

Hi guys!!

Just a tip to help people from losing extended periods of time (like I've done) it seems current versions of PostSQL don't require '10' (and slash - not showing in this comment for some reason) in the System Variables as the directory no longer seems to exist. It all seems to be working fine now!

"configure": " ./bin/configure_db.sh"

this does not work on windows, "." is not a recognized as a cmd

"configure": "sh ./bin/configure_db.sh"

In step 4.2 for macOS, what do you when you search through the output and Postgres.app/Contents/Version/latest/bin is not there? When I run echo "$PATH", I get a single line of output: /usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin, which doesn't contain Postgres anything. Not sure if it makes a difference, but I ran echo "$PATH" while the present working directory was the backend directory created in the course.

For what it's worth, step 5 seems to be running OK. When I run lsof -i tcp:5432, I get two commands called postgres with a name of localhost:postgresql, one with a type of IPv6 and the other with a type of IPv4.

Looks like I managed to answer my own question… this Stack Overflow answer led me to this section of the Postgres.app documentation, which says:

Configure your $PATH
Postgres.app includes many command line tools. If you want to use them, you must configure the $PATH variable.

The easiest way to configure your PATH is to execute the following command:

sudo mkdir -p /etc/paths.d &&
echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp

Don’t forget to close the Terminal window and open a new one for changes to take effect.

Once I did that, I was able to get psql --help and psql -U postgres to work properly.

sudo mkdir -p /etc/paths.d &&
echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp

@vincentpace Thanks this worked for me