Default port: 5432
First you must have an OS user (not a database user) named
This OS user usually is created when PostgreSQL is installed on Ubuntu via
The first login can only be done by the
postgres user, so you must do:
sudo -u postgres psql template1
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
This only works if the authentication method for
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
postgres PostgreSQL user you can create new users as:
sudo -u postgres createuser -deElPrs <my_username>
In PostgreSQL, an user is commonly called a role.
Connect to a database.
List all tables on current database:
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
To view data, select the table, right click then View Data
git clone git://git.postgresql.org/git/postgresql.git
GitHub mirror: https://github.com/postgres/postgres
Coded in C.
How many rows are there in each table
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;