Compiling and running your own Postgresql from scratch

We recommend that you  use the system version of PostgreSQL that comes with Centos and simply run a new instance, which PostgreSQL calls a "cluster". However, if you find the default installation lacking and wish to compile your own here are some instructions that may help you along the way.

Go to https://www.postgresql.org/ftp/source/
and download the latest stable (non beta version), which at the time
this was written is postgresql-11.0.tar.gz.

Create a directory to install it in: mkdir my_postgres

Move the downloaded file to that directory: mv postgresql-11.0.tar.gz $Home/my_postgres

Enter the directrory: cd $Home/my_postgres

unzip the downloaded file: gunzip postgresql-11.0.tar.gz
untar the the resulting tar file: tar xvf postgresql-11.0.tar

Now cd to the source code directory: cd postgresql-11.0

Now run ./configure: ./configure --prefix=$HOME/my_postgres

    note: You must use --prefix or later on "make install" will default to trying to
          install files in system locations for which you do not have write permission.
    note: ./configure has many other options, but for this install we will not specify any.
          To see the possible options, simply run "./configure --help".
          If you decide to add other options, be aware that it may be non-trivial to add them.
          For example: using "--with-python" will default to the version of python
          in /usr/bin even if you used module load python-3.6 just before running ./configure.
          You would have to run configure like this:
          ./configure --prefix=$HOME/my_postgres --with-python PYTHON=/usr/local/stow/python-3.6/bin/python3


Then run "make": make
  note: this will take a while

Then run "make install": make install

Now create a data directory: mkdir $HOME/my_postgres/data

Now initialize database: $HOME/my_postgres/bin/initdb -D $HOME/my_postgres/data

Now change port (from default of 5432) to some high number port over 10000:
  cd $HOME/my_postgres/data
  vi postgresql.conf (uncomment "#port = 5432" and change it.)

You can now start database server using:
$HOME/my_postgres/bin/pg_ctl -D $HOME/my_postgres/data -l logfile start


This PostgreSQL installation uses your username as Postgres admin name.
The default database created in Postgres is postgres
To login to this database use: $HOME/my_postgres/bin/psql -U $USER -p 10432 postgres


To stop server:
$HOME/my_postgres/bin/pg_ctl -D $HOME/my_postgres/data -w stop

note: for help with the commands in $HOME/my_postgres/bin: comman_name --help

To create a new database (besides the default of postgres):
$HOME/my_postgres/bin/createdb -U $USER -p 10432 my_otherdb


To create a new user that can access your postgresql server:
$HOME/my_postgres/bin/createuser -U $USER -p 10432 my_otheruser

To connect to it from python3, try this  test program.  If saved as  test.py you can simply run it as "python3 test.py"

import psycopg2
from psycopg2 import Error

try:
        connection = psycopg2.connect(user = "your_username",
                                      password = "tricky_password!",
                                      host = "127.0.0.1",
                                      port = "10432",
                                      database = "testpython")
        cursor = connection.cursor()
        # Print PostgreSQL Connection properties
        print ( connection.get_dsn_parameters(),"\n")
        # Print PostgreSQL version
        cursor.execute("SELECT version();")
        record = cursor.fetchone()
        print("You are connected to - ", record,"\n")
except (Exception, psycopg2.DatabaseError) as error :
        print ("Error while connecting to PostgreSQL", error)
finally:
        #closing database connection.
            if(connection):
                cursor.close()
                connection.close()
                print("PostgreSQL connection is closed")

   note: Postgresql installs without a password by default and you will notice in
             example python program that a password is  upplied among the arguments for the "connection".
            You can set the password for your user by connecting to the
            database ($HOME/my_postgres/bin/psql -U $USER -p 10432 postgres) and
            simply entering \password at the database prompt.
   note:  Also note that in the example program that  "your_username" could also be
              a username you create for your postgresql server using $HOME/my_postgres/bin/createuser. For
              instructions on this and a multitude of other tasks, please refer to the documentation.
   note: If you set a password for a user, this will have no effect unless you also edit
              $HOME/my_postgres/data/pg_hba.conf and change the "METHOD" from "trust" to "password" for the lines
              "METHOD" from "trust" to "password" for the lines
              "local   all             all                                     trust"
               and
              "host    all             all             127.0.0.1/32            trust"