This is version . It is not the current version, and thus it cannot be edited.
Back to current version   Restore this version

psql#

psql  [-h 192.168.1.1]  db-name  user-name

user-name is often postgres

to see all output:

psql db-name  user-name  2>&1 |tee  log

psql commands#

\q		quit
\l		list databases
\c db		connect to db
\dt		list tables
\i file		input file
\?		list \ commands
\h		list sql commands
create database mydb;
drop database mydb;

Running a report from the command line:#

psql arahant postgres -c 'select screen_id, substring(filename from 24) as "File Name", name from screen order by filename;' >output.txt

Dumping a single DB#

pg_dump [-h 192.168.1.6]  [options]  db-name  -U  user-name  |gzip  > file.dump.gz

	-s			schema only
	-a --column-inserts	data only

	no option means schema and data

To pass the password in a shell script do:

export PGPASSWORD=xxxxx

Restoring a single DB#

Everything done through psql

Drop existing stuff first if pre-existing:

	psql mydb postgres
	drop schema public cascade;
	create schema public;
Otherwise:
	psql ...  postgres postgres
	create database mydb;
	\c  mydb
Then:
	\i  file.dump

Dumping an entire (all) DB's#

pg_dumpall -U postgres |gzip >file

CSV file format#

Exporting:

copy (select lname, fname, personal_email from person where personal_email <> '')
to '/tmp/file.csv'
with (format csv);

Importing:

copy person (user_id, user_lname, user_fname, email_address, user_password, birth_date, sex, when_added)
from '/tmp/sample-users.csv'
with (format csv);

Starting & stopping the server#

su postgres
export PGDATA=/user/...
pg_ctl stop

pg_ctl -l ~/logfile start


pg_ctlcluster 8.3 main restart

Configuration#

Initially, only the postgres user can access the database. All configuration should be done from the postgres user as follows:

	su
	su postgres
Configuration on a running system can be discovered via psql using:
	show all;
	show hba_file;
	show config_file;
	show data_directory;
Do a "show all;" in psql and check lc_*. It must say "C" and not "en_US.UTF-8" or spaces and - will not be significand in sorts. If it is wrong you must delete and re-create the entire instance. (show below)

You must give postgres a password, do from psql:

	alter user postgres with password 'postgres';
Configuration changes can be recorded via:
	pg_ctl reload
	    or
	pg_ctlcluster 8.1 main reload

Access is controlled by file: pg_hba.conf

The following line must be in there for JDBC to work:

	host	all	all	127.0.0.1/32	md5
The following line allows local users to use the DB with the password
	local	all	all	md5
The following line allows local users to use the DB without a password:
	local	all	all	trust
The following line allows remote machines on the same sub-domain to use the DB with password:
	host	all	all	192.168.1.0/24	md5
Or, you can use the following to inhibit SSL communications:
	hostnossl	all	all	192.168.1.0/24	md5
The /24 means only the first 24 bits of the address (192.168.1) are significant

If other machines are to use the DB you also have to enable them via the postgresql.conf file. If this file is changed you must re-boot postgres. Making it reload is not enough. Make the following changes:

	listen_addresses = '*'
	port = 5432
listen_address doesn't list address of machines that can connect! It lists ports that postgres will listen from. This is only useful if you have multiple ethernet cards on the postgres box. Use either localhost or *.

Max connections#

The maximum number of connections allowed on the database is controlled by the postgresql.conf file via the max_connections setting. In general, each web service uses one connection. The default maximum is 100. If this setting is changed the database must be stopped and restarted.


Creating a database#

When creating the DB do:

	initdb  --locale=C
If you don't do this (--locale=C) the system will ignore leading spaces and `-' when sorting.

Add new attachment

Only authorized users are allowed to upload new attachments.
« This particular version was published on 24-Jul-2013 15:47 by BlakeMcBride.