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/
The Debian installation is straight forward:
apt-get install postgresql
Post installation steps:
- set a password for the superuser
- adjust the configuration as needed
- check logfiles:
Be aware of the following defaults:
- PostgreSQL will listen on
- 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.
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
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
psqlis the CLI client (in the MySQL world you would use 'mysql')
pg_dumpis the backup client (equivalent to 'mysqldump')
createdbto create a database and 'dropdb' to drop it
pg_lsclustersobtain some basic information
pg_ctlclustercontrol 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 (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
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
If you want to dig deeper head to:
How to get started:
List all databases:
List all databases with disk usage:
Connect to a database:
Show tables in database:
… or all tables with disk usage: