PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS X.^[4] It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software. PostgreSQL is developed by the PostgreSQL Global Development Group, consisting of a handful of volunteers employed and supervised by companies such as Red Hat and EnterpriseDB.^[5] It implements the majority of the SQL:2008 standard,^[6] is ACID-compliant, is fully transactional (including all DDL statements), has extensible data types, operators, index methods, functions, aggregates, procedural languages, and has a large number of extensions written by third parties.

The vast majority of Linux distributions have PostgreSQL available in supplied packages. Mac OS X, starting with Lion, has PostgreSQL server as its standard default database in the server edition,^[7][8] and PostgreSQL client tools in the desktop edition.

PostgreSQL has bindings for many programming languages such as C, C++, Python, Java, PHP, Ruby… It can be used to power anything from simple web applications to massive databases with millions of records.

Client Installation

If you only wish to connect to a PostgreSQL server, do not install the whole PostgreSQL package, but install the PostgreSQL client instead. To do this, use the following command

 sudo apt-get install postgresql-client

you then connect to the server with the following command

 psql -h server.domain.org database user

After you inserted the password you access PostgreSQL with line commands. You may for instance insert the following

 SELECT * FROM table WHERE 1;

You exit the connection with

 q

Installing PostgreSQL Database

1. Install PostgreSQL using the apt-get command in gnome-terminal:

   1	sudo apt-get install postgresql libpq-dev

2. After installation is complete, change user to the PostgreSQL user:

   1	sudo su - postgres

3. You are now working as the PostgreSQL user. Now, let’s change your database password to be more robust. In this example, I’m setting the password as “s0meth1ng”:

   1 2 3 4

   ~$: psql -d postgres -U postgres psql (9.1.3) Type "help" for help. postgres=# alter user postgres with password 's0meth1ng'; ALTER ROLE postgres=# q

4. Restart the PostgreSQL database to let the changes take effect:

   1	sudo /etc/init.d/postgresql restart

Installing and Setting Up pgAdmin III

1. Install pgAdmin III using the apt-get command in gnome-terminal:

   1	sudo apt-get install pgadmin3

2. Once installed, you can launch pgAdmin III by quick-launching it in Alt-F2, then typing pgadmin3.
3. Now, let’s add a new PostgreSQL database server to the list of servers. Go to File > Add Server, and enter the details as the following screenshot:
   
4. Once that is done, you’ll now see your new server at the list of servers on the left pane. Go ahead, and create your database. Have fun!
   

Administration

pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line:

 sudo apt-get install pgadmin3

You may also use the Synaptic package manager from the System>Administration menu to install these packages.

Basic Server Setup

To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command.

In a terminal, type:

sudo -u postgres psql postgres

Set a password for the “postgres” database role using the command:

password postgres

and give your password when prompted. The password text will be hidden from the console for security purposes.

Type Control+D to exit the posgreSQL prompt.

Create database

To create the first database, which we will call “mydb”, simply type:

 sudo -u postgres createdb mydb

Install Server Instrumentation for Postgresql 8.4 or 9.1

To install Server Instrumentation, you must install postgresql-contrib:

 sudo apt-get install postgresql-contrib

For “”Postgresql 9.1″”+ install the adminpack “extension”:

 sudo -u postgres psql
 CREATE EXTENSION adminpack;

Alternative Server Setup

If you don’t intend to connect to the database from other machines, this alternative setup may be simpler.

By default in Ubuntu, Postgresql is configured to use ‘ident sameuser’ authentication for any connections from the same machine. Check out the excellent Postgresql documentation for more information, but essentially this means that if your Ubuntu username is ‘foo’ and you add ‘foo’ as a Postgresql user then you can connect to the database without requiring a password.

Since the only user who can connect to a fresh install is the postgres user, here is how to create yourself a database account (which is in this case also a database superuser) with the same name as your login name and then create a password for the user:

 sudo -u postgres createuser --superuser $USER
 sudo -u postgres psql

 postgres=# password $USER

Client programs, by default, connect to the local host using your Ubuntu login name and expect to find a database with that name too. So to make things REALLY easy, use your new superuser privileges granted above to create a database with the same name as your login name:

 createdb $USER

Connecting to your own database to try out some SQL should now be as easy as:

 psql

To create a database with a user that have full rights on the database, use the following command:

sudo -u postgres createuser -D -A -P myuser
sudo -u postgres createdb -O myuser mydb

The first command line creates the user with no database creation rights (-D) with no add user rights -A) and will prompt you for entering a password (-P). The second command line create the database ‘mydb with ‘myuser‘ as owner.

This little example will probably suit most of your needs. For more details, please refer to the corresponding man pages or the online documentation.

$ psql -d postgres
postgres=# create role app_name login createdb;
postgres=# q

SQL Dump

The idea behind this dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. PostgreSQL provides the utility program pg_dump for this purpose. The basic usage of this command is:

pg_dump dbname > outfile

As you see, pg_dump writes its result to the standard output. We will see below how this can be useful.

pg_dump is a regular PostgreSQL client application (albeit a particularly clever one). This means that you can perform this backup procedure from any remote host that has access to the database. But remember that pg_dump does not operate with special permissions. In particular, it must have read access to all tables that you want to back up, so in practice you almost always have to run it as a database superuser.

To specify which database server pg_dump should contact, use the command line options -h host and -p port. The default host is the local host or whatever your PGHOST environment variable specifies. Similarly, the default port is indicated by the PGPORT environment variable or, failing that, by the compiled-in default. (Conveniently, the server will normally have the same compiled-in default.)

Like any other PostgreSQL client application, pg_dump will by default connect with the database user name that is equal to the current operating system user name. To override this, either specify the -U option or set the environment variable PGUSER. Remember that pg_dump connections are subject to the normal client authentication mechanisms (which are described in Chapter 19).

An important advantage of pg_dump over the other backup methods described later is that pg_dump’s output can generally be re-loaded into newer versions of PostgreSQL, whereas file-level backups and continuous archiving are both extremely server-version-specific. pg_dump is also the only method that will work when transferring a database to a different machine architecture, such as going from a 32-bit to a 64-bit server.

Dumps created by pg_dump are internally consistent, meaning, the dump represents a snapshot of the database at the time pg_dump began running. pg_dump does not block other operations on the database while it is working. (Exceptions are those operations that need to operate with an exclusive lock, such as most forms of ALTER TABLE.)

psql dbname < infile

psql --set ON_ERROR_STOP=on dbname < infile

pg_dump -h host1 dbname | psql -h host2 dbname

pg_dumpall > outfile

psql -f infile postgres

pg_dump dbname | gzip > filename.gz

gunzip -c filename.gz | psql dbname

cat filename.gz | gunzip | psql dbname

pg_dump dbname | split -b 1m - filename

cat filename* | psql dbname

pg_dump -Fc dbname > filename

pg_restore -d dbname filename

psql -d myDataBase -a -f myInsertFile

Have three choices to supply a password:

1. set the PGPASSWORD environment variable. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-envars.html
2. use a .pgpass file to store the password. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-pgpass.html
3. use “trust authentication” for that specific user: http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST