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"