PostgreSQL Cheat
Open source.
Default port: 5432
Get started
http://stackoverflow.com/questions/1471571/how-to-configure-postgresql-for-the-first-time
First you must have an OS user (not a database user) named postgres
.
This OS user usually is created when PostgreSQL is installed on Ubuntu via apt-get
.
The first login can only be done by the postgres
user, so you must do:
sudo -u postgres psql template1
Where template1
is the name of a database which always exists on new installations. If not given, login may fail, since it tries to use a default database with the same name as the username postgres
.
This only works if the authentication method for postgres
is peer
.
peer
means that if the OS user named XXX
is logged in, then he can automatically login on the database if XXX
exists on the database, without having a database password.
The other major method of authentication is md5
, which can work for users that don’t exist in the OS and requires a password. This method is safer, but you will have to type more every time.
This can be set under the following configuration file:
sudo vim /etc/postgresql/9.1/main/pg_hba.conf
Your login method is probably being controlled by the user all
line in the default config file.
Don’t forget to restart PG after you have edited the configuration file:
sudo service postgresql restart
Using the postgres
PostgreSQL user you can create new users as:
sudo -u postgres createuser -deElPrs <my_username>
Role
In PostgreSQL, an user is commonly called a role.
Slash commands
h
Get help:
\h
USE
c
Connect to a database.
\c database_name
SHOW TABLES
dt
List all tables on current database:
\dt
Mnemonic: Database Tables
.
DESC
d
Describe table:
\d+
Tools
pgAdmin
pgAdmin is a popular GUI (not browser based) tool to view and edit Pg tables.
Ubuntu 12.04 install:
suto apt-get install -y pgadmin3
Run:
pgadmin3
To view data, select the table, right click then View Data
Internals
Source code:
git clone git://git.postgresql.org/git/postgresql.git
http://www.postgresql.org/docs/9.3/static/git.html
GitHub mirror: https://github.com/postgres/postgres
Coded in C.
Dead rows
https://www.postgresql.org/docs/9.1/static/sql-vacuum.html
How many rows are there in each table
Estimate:
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;