Installing/Using PostgreSQL Environment setup for running PostgreSQL in COMP9315: # Must be "source"d from sh, bash, ksh, ... # can be any directory PGHOME=/home/jas/srvr/pgsql # data does not need to be under $PGHOME export PGDATA=$PGHOME/data export PGHOST=$PGDATA export PGPORT=5432 export PATH=$PGHOME/bin:/home/cs9315/bin:$PATH # /home/cs9315/bin/pgs simplifies managing server Will probably work (with tweaks) on home laptop if Linux or MacOS Brief summary of installation: $ tar xfj ..../postgresql/src.tar.bz2 # create a directory postgresql-12.5 $ source your/environment/file # set up environment variables $ configure --prefix=$PGHOME $ make $ make install $ initdb # set up postgresql configuration ... done once? $ edit postgresql.conf $ pg_ctl start -l $PGDATA/log # do some work with PostgreSQL databases $ pg_ctl stop

On CSE machines, <@>~cs9315/bin/pgs can simplify some things Using PostgreSQL for Assignments If changes don't modify storage structures ... $ edit source code $ pg_ctl stop $ make $ make install $ pg_ctl start -l $PGDATA/log # run tests, analyse results, ... $ pg_ctl stop

In this case, existing databases will continue to work ok. If changes modify storage structures ... $ edit source code $ save a copy of postgresql.conf $ pg_dump -O -x testdb > testdb.dump $ pg_ctl stop $ make $ make install $ rm -fr $PGDATA $ initdb $ restore postgresql.conf $ pg_ctl start -l $PGDATA/log $ createdb testdb $ psql testdb -f testdb.dump # run tests and analyse results

Old databases will not work with the new server. Troubleshooting ... read the <@>$PGDATA/log file which socket file are you trying to connect to? check the <@>$PGDATA directory for socket files remove <@>postmster.pid if sure no server running ... Prac Exercise P01 has useful tips down the bottom Installing PostgreSQL PostgreSQL is available via the COMP9315 web site.

Provided as tar-file in <@>~cs9315/web/21T1/postgresql/

File: <@>src.tar.bz2 is ~20MB **

Unpacked, source code + binaries is ~130MB **

If using on CSE, do not put it under your home directory

Place it under <@>/srvr/YOU/ which has 500MB quota

Most efficient to run server on <@>grieg Before Installing ... If you have databases from previous DB courses the databases may not work under v12.5 to preserve them, use dump/restore E.g. ... login to grieg ... ... run your old server for the last time ... $ pg_dump -O -x myFavDB > /srvr/YOU/myFavDB.dump ... stop your old server for the last time ... ... remove data from your old server ... $ rm -fr /srvr/YOU/pgsql ... install and run your new PostgreSQL 12.5 server ... $ createdb myFavDB $ psql myFavDB -f /srvr/YOU/myFavDB.dump ... your old database is restored under 12.5 ...