PostgreSQL

PostgreSQL is pronounced “post gress queue ell”.

These are my basic PostgreSQL infos. Coming from MySQL I focused on the PostgreSQL equivalents to what I know from MySQL.

For an comprehensive comparison see MySQL vs PostgreSQL

The excellent documentation lives here: http://www.postgresql.org/docs/

Installation

The Debian installation is straight forward:

apt-get install postgresql

Post installation steps:

  • set a password for the superuser postgres
  • adjust the configuration as needed
  • check logfiles: /var/log/postgresql/

Be aware of the following defaults:

  • PostgreSQL will listen on 127.0.0.1:5432
  • default cluster: main
  • default database: template1
  • default schema: public

A few words about PostgreSQL “clusters”:
Before you can do anything, you must initialize a database storage area on disk. That's what is called a database cluster (a collection of databases managed by a single instance of a running database server).

In file system terms, a database cluster will be a single directory under which all data will be stored.

Schema
You can think of Schemas as something like directories at the OS level. But you can't create “subdirectories” or nest schemas.

If you don't specifiy any schema, the “public” schema will be automatically selected.

Schemas are useful to organize access to databases or group database objects into logical groups.

If you're still confused, I recommend reading the official docs: http://www.postgresql.org/docs/9.1/static/ddl-schemas.html

One last thing about schemas:
you'll find the system catalog schema with all the tables and buil-in data types, functions and operators in the pg_catalog schema.

Configuration

Where the MySQL configuration is somewhat “flat” PostgreSQL comes with several files:

/etc/postgresql
└── 9.1
    └── main
        ├── environment
        ├── pg_ctl.conf
        ├── pg_hba.conf
        ├── pg_ident.conf
        ├── postgresql.conf
        └── start.conf

2 directories, 6 files

CLI Tools

  • psql is the CLI client (in the MySQL world you would use 'mysql')
  • pg_dump is the backup client (equivalent to 'mysqldump')
  • createuser or 'dropuser'
  • createdb to create a database and 'dropdb' to drop it
  • pg_lsclusters obtain some basic information
  • pg_ctlcluster control cluster (start|stop|restart|reload|promote)

To work with the CGI tool psql it is best practice to do this as the postgres system user:

# if you're root, become postgres:
su - postgres

psql

psql (9.1.14)
Type "help" for help.

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

pg_lsclusters

Version Cluster   Port Status Owner    Data directory                     Log file
9.1     main      5432 online postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log

Users, Roles and Databases

The default client authentication setup allows any local user to connect to the database and even become the database superuser.

You will need to become the operating system user under which PostgreSQL was installed (usually postgres) to create the first user account.

This user has superuser status. A database superuser bypasses all permission checks, except the right to log in or the right to initiate replication.

postgres, the superuser
You'll want to create a password for this user. Enter psql as usual and set the password:

psql (9.1.14)
Type "help" for help.

postgres=# \password postgres
Enter new password:
Enter it again:
postgres=#

Access from localhost via Unix-domain sockets would still be possible without a password as long as you don't edit the default pg_hba.conf file.

If you want to dig deeper head to:

psql

How to get started:

List all databases:

\l

List all databases with disk usage:

\l+

Connect to a database:

\c db-name

Show tables in database:

\dt

… or all tables with disk usage:

\dt+

Describe table:

\d tablename

Quit psgl:

\q
postgres.txt · Last modified: 2015-08 by tb
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0 ipv6 ready