![YoLinux.com Tux database image](images/tux-DB-t3.gif)
Ubuntu: (16.04, 14.04) Install: sudo apt-get install postgresql
- postgresql-9.X - libraries and SQL
- postgresql-common - the database program
- postgresql-client-9.X - utility programs and man pages
- postgresql-client-common - utility programs and man pages
- libpq5 - network client libraries
Starting the database: sudo service postgresql start
Red Hat Enterprise Linux 6 RPM packages:
- postgresql-8.4.11-1.el6_2.x86_64 - commands, HTML docs and man pages
- postgresql-server-8.4.11-1.el6_2.x86_64 - DB server and locale based messages
- postgresql-libs-8.4.11-1.el6_2.x86_64 - libraries and locale based messages
- postgresql-docs-8.4.11-1.el6_2.x86_64 - tutorials, examples and a monster PDF manual
- postgresql-test - lots of examples.
- postgresql-jdbc - Java connectivity
- postgresql-plperl - Perl connectivity
- postgresql-plpython - Python connectivity
- postgresql-devel - C language connectivity
Starting the database (as root): service postgresql start
The first time this is run you will get the following error:
/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
[FAILED]
To initialize the system for the first run (as root): service postgresql initdb
Initializing database: [ OK ]
Once the database initialization has occurred, one can then start the database (as root): service postgresql restart
Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ]
The user "postgres" should have already been configured by the installation of the RPMs. Info:
- User: postgres
- Home directory: /var/lib/pgsql
- Default shell: /bin/bash
- Login as user postgres: su - postgres
This will execute the profile: /var/lib/pgsql/.bash_profilePGLIB=/usr/lib/pgsql PGDATA=/var/lib/pgsql/data export PGLIB PGDATA
- Initialize PostgreSQL database server: initdb --pgdata=/var/lib/pgsql/data
This creates a bunch of directories, a template directory and sets up the postgres configuration in the user directory /var/lib/pgsql/. Red Hat start command (service)/script (rc script) will perform this task if it has not already been performed. See next step - Starting the database. - Starting the database server: As root. (from most to least favorite method)
service postgresql start
(If the database has not already been initialized with initdb, this will be performed by the command)
OR
/etc/rc.d/init.d/postgresql start
(If the database has not already been initialized with initdb, this will be performed by the script)
OR
/usr/bin/pg_ctl -D /var/lib/pgsql/data -p /usr/bin/postmaster -l logfile start &
OR
/usr/bin/postmaster -D /var/lib/pgsql/data &- Configuration file: /var/lib/pgsql/data/postgresql.conf
By default there is no network access. See the directive tcpip_socket. (Required for ODBC,JDBC) Also see the postmaster directive "-i". Logging and tuning parameters are specified here. - Host Access file: /var/lib/pgsql/data/pg_hba.conf
- Authentication/Identification file: /var/lib/pgsql/data/pg_ident.conf
- Configuration file: /var/lib/pgsql/data/postgresql.conf
- Create a database: /usr/bin/createdb bedrock
(As Linux user postgres: sudo su - postgres) - Connect to the database: /usr/bin/psql bedrock
Execute command as Linux user postgres
You will now be at the PostgreSQL command line prompt.[prompt]$ psql - or "psql bedrock" Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit bedrock=# bedrock=# \c bedrock You are now connected to database bedrock. bedrock=# create table employee (Name char(20),Dept char(20),jobTitle char(20)); CREATE TABLE bedrock=# INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger'); INSERT 0 1 bedrock=# INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst'); bedrock=# INSERT into employee VALUES ('Barney Rubble','Sales','Neighbor'); bedrock=# INSERT INTO employee VALUES ('Betty Rubble','IT','Neighbor'); bedrock=# SELECT * from employee; name | dept | jobtitle ----------------------+----------------------+---------------------- Fred Flinstone | Quarry Worker | Rock Digger Wilma Flinstone | Finance | Analyst Barney Rubble | Sales | Neighbor Betty Rubble | IT | Neighbor (4 rows) bedrock=# bedrock=# \q
- Database discovery / Examine a database (as user postgres: su - postgres):
[postgres]$ psql- \l :List databases
- \c database-name :Connect to database
- \c :Show the database your are connected to
- \d :List tables in database
- \d table-name :Describe table
- SELECT * FROM table-name :List table contents
- More info:
- Create a user:
- Command line: [prompt]$ createuser dude
- SQL: CREATE USER dude WITH PASSWORD 'supersecret';
Change with ALTER USER
- Grant priveliges:
- SQL: GRANT UPDATE ON table-name to dude
- SQL: GRANT SELECT ON table-name to dude
- SQL: GRANT INSERT ON table-name to dude
- SQL: GRANT DELETE ON table-name to dude
- SQL: GRANT RULE ON table-name to dude
- SQL - Do it all: GRANT ALL PRIVILEGES ON table-name to public
- Delete a user:
- Command line: [prompt]$ dropuser SuperDude
- Delete a database:
- Command line:
- [prompt]$ destroydb bedrock
- [prompt]$ dropdb bedrock
- SQL: DROP DATABASE bedrock;
- Command line:
- Create a database:
- Command line: [prompt]$ createdb bedrock -U user-name -W
You will be prompted for a password. (or execute as Linux user postgres without -U and -W options) - SQL: CREATE DATABASE bedrock
- Command line: [prompt]$ createdb bedrock -U user-name -W
- Version Upgrades:
- Dump: [prompt]$ postgresql-dump -t /var/lib/pgsql/backup/db.bak -p /var/lib/pgsql/backup/old -d
- Restore: [prompt]$ psql -e template1 < /var/lib/pgsql/backup/db.bak
The table template1 is the default administrative database.
- Create a user:
postgres=# CREATE DATABASE bedrock; CREATE DATABASE postgres=# \c bedrock You are now connected to database "bedrock" as user "postgres". bedrock=# CREATE USER dude WITH PASSWORD 'supersecret'; CREATE ROLE bedrock=# GRANT ALL PRIVILEGES ON employee to dude; GRANT bedrock=# \z Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+----------+-------+---------------------------+-------------------+---------- public | employee | table | postgres=arwdDxt/postgres+| | | | | dude=arwdDxt/postgres | | (1 row)
User GUI interfaces to the database are often written with web based frameworks like PHP or Java with a servlet back-end. See the YoLinux tutorial on accessing PostgreSQL with JDBC in a Tomcat servlet example.
Links/Info:
- PostgreSQL additional info and documentation:
- Resources: /usr/share/pgsql/
Backup / Dump a database:
- [prompt]$ pg_dump dbname > outfile
- [prompt]$ pg_dump dbname -h localhost -U postgres -t tablename > outfile
if migrating the resulting SQL into another SQL database, add the option: --no-owner. This eliminates the ALTER OWNER or SET SESSION AUTHORIZATION statements. - [prompt]$ pg_dumpall > outfile
- [prompt]$ pg_dumpall -Fc dbname > outfile
Restore a database:
- [prompt]$ psql dbname < infile
where infile is the file output by the pg_dump command. The database dbname will not be created by this command but can be generated with: createdb dbname - [prompt]$ psql -f infile postgres
if you are loading into an empty cluster then "postgres" should usually be used.
pgadmin:
PgAdmin is a GUI PostgreSQL administration tool which allows one to monitor database server activity, behavior, health as well as administer users, schemas and data. Advanced functions such as procedural language debugging, backups and permissions management make PgAdmin indespensible.
Install:- Ubuntu: sudo apt install pgadmin3
Note that pgadmin3 does not allow null passwords while psql will, thus the default no-password for user "postgres" will not work. Assign a password using psql with the database command: ALTER USER postgres WITH PASSWORD 'supersecret';
Run:- Allow X-Windows session to display session generated by another user (postgres): xhost +
- Switch user to postgres: sudo su - postgres
- pgadmin3
- File + Add Server:
- Traverse the "bedrock" database tree to show the table "employee":
- With the "employee" table highlighted in the tree, select the grid icon "View the data in the selected object":
Home Page: PgAdmin.org - documentation
Linux shell commands:
- createdb - Create a new PostgreSQL database
- createlang - Add a new programming language to a PostgreSQL database
- createuser - Create a new PostgreSQL user
- dropdb - Remove an existing PostgreSQL database
- droplang - Remove a programming language from a PostgreSQL database
- dropuser - Drops (removes) a PostgreSQL user
- pg_dump - Extract a PostgreSQL database into a script file. Example: pg_dump dbname > outfile
- pg_dumpall - Extract all PostgreSQL databases into a script file
- vacuumdb - Clean and analyze a PostgreSQL database
- psql - PostgreSQL interactive terminal
PostgreSQL SQL Statement Man Pages:
- abort - abort the current transaction
- alter group - change role name or membership
- alter table - change the definition of a table
- alter user - change a database role
- begin - start a transaction block
- checkpoint - force a transaction log checkpoint
- close - close a cursor
- cluster - cluster a table according to an index
- comment - define or change the comment of an object
- commit - commit the current transaction
- copy - copy data between a file and a table
- create aggregate - define a new aggregate function
- create constraint trigger - define a new constraint trigger
- create database - create a new database
- create function - define a new function
- create group - define a new database role
- create index - define a new index
- create language - define a new procedural language
- create operator - define a new operator
- create rule - define a new rewrite rule
- create sequence - define a new sequence generator
- create table - define a new table
- create table as - define a new table from the results of a query
- create trigger - define a new trigger
- create type - define a new data type
- create user - define a new database role
- create view - define a new view
- declare - define a cursor
- delete - delete rows of a table
- drop aggregate - remove an aggregate function
- drop database - remove a database
- drop function - remove a function
- drop group - remove a database role
- drop index - remove an index
- drop language - remove a procedural language
- drop operator - remove an operator
- drop rule - remove a rewrite rule
- drop sequence - remove a sequence
- drop table - remove a table
- drop trigger - remove a trigger
- drop type - remove a data type
- drop user - remove a database role
- drop view - remove a view
- end - commit the current transaction
- explain - show the execution plan of a statement
- fetch - retrieve rows from a query using a cursor
- grant - define access privileges
- insert - create new rows in a table
- listen - listen for a notification
- load - load or reload a shared library file
- lock - Unimplemented system call
- move - move curses window cursor
- notify - generate a notification
- reindex - rebuild indexes
- reset - restore the value of a run-time parameter to the default value
- revoke - remove access privileges
- rollback - abort the current transaction
- select - retrieve rows from a table or view
- select into - define a new table from the results of a query
- set - change a run-time parameter
- set constraints - set constraint checking modes for the current transaction
- set transaction - set the characteristics of the current transaction
- show - show the value of a run-time parameter
- truncate - empty a table or set of tables
- unlisten - stop listening for a notification
- update - update rows of a table
- vacuum - garbage-collect and optionally analyze a database
- Postgresql.org
- pgAdmin.org - GUI admin tool
- Devart: PostgreSQL ODBC Driver - commercial ODBC Driver for Linux