psql - command line database query tool#

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

user-name is often postgres

to save 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
\d table        display schema for table
\i file		input file
\?		list \ commands
\h		list sql commands
show all;
show server_version;
show config_file;
show data_directory;
show hba_file;
create database mydb;
drop database mydb;

Data Types#

smallint 2
integer 4
bigint 8
real 4
double precision 8
smallserial 2
serial 4
bigserial 8
money 8
char(n)
varchar(n)
text variable length, unlimited
bytea binary, variable length
timestamp date and time not null default current_timestamp
date

Passing a password into psql#

export PGPASSWORD=ThePassword

Running a report from the command line:#

psql mydb 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);

The csv files must be in the /tmp directory or somewhere that is globally readable because this command is run by a process that is connected to the postgres user. It, therefore, can only access files that it has permission to read.

The import command may exclude the column list if all the columns are used and all of the columns are in the order specified in the schema.


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 page (revision-9) was last changed on 19-Jun-2017 12:03 by BlakeMcBride