Using The Postgresql Database on Linux
- last revision
- 27 October 2011, 6:33pm
- book quality
,
This book purports to provide concise and practical recipes for
using the open-source relational (sql) database systems Postgresql on linux
- www: www.postgresql.org
-
the official site
- www: http://www.cyberciti.biz/faq/psql-fatal-ident-authentication-failed-for-user/
-
a good article about authentication problems (i.e. you cant logon)
Watch postgresql calls from your application on localhost
sudo tcpdump -nnvvXSs 1514 -i lo0 dst port 5432
Installing Postgresql ‹↑›
install the postgresql database on a debian-style distribution
sudo apt-get install postgresql
Authentication ‹↑›
The process of logging onto something is generally referred to
as 'authentication'. Postgresql uses reasonably complicated authentication
which may cause some head-scratching.
switch user to the 'postgres' user, in order to administer the server
sudo su postgres
psql -U postgres
find out where the authentication configuration file is
find / -name 'pg_hba.conf'
locate pg_hba.conf
Stopping And Starting The Server ‹↑›
check that the postgresql database server is running
ps ax | grep postgres
ps ax | grep postmaster
pg_ctl status
sudo /etc/init.d/postgresql-8.4 status
start the postgresql database server
sudo /etc/init.d/postgresql-8.4 start
pg_ctl start
pg_ctl -w start
the default 8.4 data folder on ubuntu
/var/lib/postgresql/8.4/main
start a postgresql server listening on tcp port 5433
pg_ctl -o "-p 5433" start
shutdown the postgre database server
pg_ctl stop
sudo /etc/init.d/postgresql-8.4 stop
restart the database server
pg_ctl restart
sudo /etc/init.d/postgresql-8.4 restart
Batch Sql Statements ‹↑›
Run gunzipped sql file in PostGres, adding to the library.
gzip -dc /tmp/pavanlimo.gz | psql -U user db
Viewing Data ‹↑›
view all data from the table 'trees'
SELECT * FROM trees;
display table data with extra ascii borders
\pset border 2
SELECT * FROM trees;
display table data with no ascii borders
\pset border 0
Tables ‹↑›
view the definition (columns) of the table 'offers'
\d offers
Creating Tables ‹↑›
create a new table 'newtable' with 2 fields (columns)
testdb=> CREATE TABLE newtable (
testdb(> first integer not null default 0,
testdb(> second text)
testdb-> ;
postgresql SQL to show count of ALL tables (relations) including
SELECT relname, reltuples, pg_relation_size(relname) FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid) WHERE relkind = 'r' AND n.nspname = 'public' ORDER BY relname;
for x in `psql -e\l | awk '{print $1}'| egrep -v
for x in `psql -e\l | awk '{print $1}'| egrep -v "(^List|^Name|\-\-\-\-\-|^\()"`; do pg_dump -C $x | gzip > /var/lib/pgsql/backups/$x-nightly.dmp.gz; done
Databases ‹↑›
list databases on the database server
psql -l
List all PostgreSQL databases. Useful when doing backups
psql -U postgres -lAt | gawk -F\| '$1 !~ /^template/ && $1 !~ /^postgres/ && NF > 1 {print $1}'
create a postgresql database called 'company'
createdb company
delete the database called 'company'
dropdb company
The Psql Utility ‹↑›
connect to the 'test' database on the database server
psql test
psql
list all databases on the database server
psql -l
connect to the 'test' database on the server using the role (user/group) 'bob'
psql -U bob -d test
psql commands
\q - quit the psql interpreter |
\du - show database roles |
\l - list all databases |
User Accounts ‹↑›
create a new postgre sql 'role' called 'bob'
createuser bob
pg:>> CREATE ROLE bob;
eliminate the postgresql role 'bob'
dropuser bob
pg:>> DROP ROLE bob;
pg:>> drop role bob;
show current roles within the database server
pg:>> \du
pg:>> SELECT rolname FROM pg_roles;
create a new database role 'bob' which can create databases, users and login
CREATE ROLE bob LOGIN CREATEDB CREATEROLE;
Backups ‹↑›
make pgsql backup and gzip it
pg_dump otrs2 | gzip > dump.gz
dump database from postgresql to a file
pg_dump -Ft -b -Uusername -hdb.host.com db_name > db.tar
Export/Backup a PostgreSQL database
pg_dump -U postgres [nomeDB] > db.dump