Postgres

Postgres 101

End all SQL commands with ; !! or otherwise postgres won't do anything. no errors. even bad commands, gibberish, no errors!!

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

Shutdown DB



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
DB is likely not secured by default, should reset the internal password (dba):



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.


"ting"
"ting"