Compiling and running your own PostgreSQL server

 

 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"