Postgres

Postgres 101

Setting up the program on FEDORA FC5

Install the packages

(sys admin work, require root priv)
yum install mysql (bunch of these)
	mysql-server... is server portion
	mysql-5... is client package, client is also needed in server to run commands.

Initialize the DB

Create User

**** su to postgres user account
-bash-3.2$ createuser --no-superuser --createdb --createrole --login --pwprompt --encrypted sys_openbis

--pwprompt will ask for password for use with newly created user.
#the last password prompt is postgres superuser password to connect to db and actually carry out the action.


-bash-3.2$ createuser --no-superuser --no-createdb --no-createrole --login --pwprompt --encrypted galaxy
Enter password for new role: 
Enter it again: 
CREATE ROLE
-bash-3.2$ createdb --owner galaxy galaxydev
CREATE DATABASE



Location of datbase files:
/var/lib/pgsql  
/var/lib/postgresql

Start the DB

su - postgres
pg_ctl -D /var/lib/pgsql/13/data start

Shutdown DB

su - postgres
pg_ctl -D /var/lib/pgsql/13/data status
pg_ctl -D /var/lib/pgsql/13/data stop

Verify status

ps -ef should show running processes like: postgres: ... 

case of SQL commands don't matter, can be all lower case.
written in uppercase here cuz of convention.

Accounts

psql -d template1			# connects to db called "template1"
ALTER USER postgres WITH PASSWORD '';	# set pw to blank.  not recommended!
ALTER USER openbiz  WITH PASSWORD '';

Secure Postgres Installation

Benchmark Postgres

Checking for installed DB

Add new user

SQL queries using pgsql

\du      # list user

what it does:
SELECT u.usename AS "User name",
  u.usesysid AS "User ID",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
       WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN CAST('create database' AS
pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;


\list     # list all db    ... but long names are truncated...

\c   dbname   # connect to a db
\dt       # list all tables in current db

\?    # list \ commands


rename db":

ALTER DATABASE openbis_prod RENAME TO openbis_prod_pre20130711 ;

DROP DATABASE openbis_prod ;



(instead of dropping a database :)

Examples

Running External .sql script

psql script.sql		# assume script is fully self-contained
psql script1.sql script2.sql 

# if need to connect to db first, then run it inside psql shell:

psql
\c openbis_prod
\i script.sql

Postgres Backup, Migration

File Formats

DB Dump

Ref

Tools

[Doc URL: http://tin6150.github.io/psg/psg/postgres.html]
(cc) Tin Ho. See main page for copyright info.

---