PostgreSQL QuickStart/Reference Commands...

Submitted by sandip on Sat, 04/15/2006 - 19:47

PostgreSQL

Since I don't use PostgreSQL as often as MySQL, I tend to forget simple commands for administering the database. Hopefully these notes will help as reference when working with PostgreSQL:

Login as "postgres" (SuperUser) to start using database:
```
# su - postgres
```
Create a new database:
```
$ createdb mydb
```
Drop database:
```
$ dropdb mydb
```
Access database:
```
$ psql mydb
```
Get help:
```
mydb=# \h
```
Quit:
```
mydb=# \q
```
Read command from file:
```
mydb=# \i input.sql
```
To dump a database:
```
$ pg_dump mydb > db.out
```
To reload the database:
```
$ psql -d database -f db.out
```

Dump all database:

# su - postgres
# pg_dumpall > /var/lib/pgsql/backups/dumpall.sql

Restore database:

# su - postgres
# psql -f /var/lib/pgsql/backups/dumpall.sql mydb

Show databases:
```
#psql -l
or
mydb=# \l;
```
Show users:
```
mydb=# SELECT * FROM "pg_user";
```
Show tables:
```
mydb=# SELECT * FROM "pg_tables";
```

Set password:

mydb=# UPDATE pg_shadow SET passwd = 'new_password' where usename = 'username';

Clean all databases (Should be done via a daily cron):
```
$ vacuumdb --quiet --all
```

postgresql development tips

Submitted by sandip on Wed, 10/12/2011 - 15:05.

plpgsql development tips

Buffer hit cache ratio

Submitted by sandip on Tue, 06/14/2011 - 16:01.

Turn on stats_block_level in "postgresql.conf" first, then run the below query:

SELECT datname,
  blks_read,
  blks_hit,
  round((blks_hit::float / (blks_read+blks_hit+1) * 100)::numeric, 2) as cachehitratio
FROM pg_stat_database
WHERE datname !~ '^(template(0|1)|postgres)$'
ORDER BY cachehitratio desc;

free space map

Submitted by sandip on Wed, 02/16/2011 - 19:16.

The best way to make sure you have enough FSM pages is to periodically vacuum the entire installation using vacuum -av and look at the last two lines of output. You want to ensure that max_fsm_relations is at least as large as the larger of 'pages stored' or 'total pages needed'.

Reference: free space map

As a very rough guide start with "max_fsm_pages" set to the sum of relpages in pg_class:

select sum(relpages) from pg_class;

list queries running

Submitted by sandip on Wed, 02/16/2011 - 13:03.

Turn on stats_command_string in "postgresql.conf" first, then run the below query:

select * from pg_stat_activity;

Additional Reference: viewing current postgresql queries

show all variables

Submitted by sandip on Wed, 02/16/2011 - 12:52.

show all;

List database size

Submitted by sandip on Mon, 02/07/2011 - 13:07.

for i in `psql -t -c "select datname from pg_catalog.pg_database"`; do echo $i; psql -t -c "select pg_size_pretty(pg_database_size('$i'))"; done

Drop table

Submitted by sandip on Wed, 04/01/2009 - 23:12.

DROP TABLE name [, ...] [ CASCADE ]

DROP TABLE removes tables from the database. Only its owner may destroy a table. To empty a table of rows, without destroying the table, use DELETE.

DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified. CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.

Installing PL/pgSQL

Submitted by Anonymous on Fri, 11/14/2008 - 12:03.

Reference:
OnLamp

To get started with PL/pgSQL, first make sure it's installed in your PostgreSQL database. If it was a part of the template1 database when your database was created, it will already be installed. To see whether you have it, run the following in the psql client:

SELECT true FROM pg_catalog.pg_language WHERE lanname = 'plpgsql';

If the result row has the value true, PL/pgSQL is already installed in your database. If not, quit psql and execute the command:

$ createlang plpgsql database_name

To add a language, you must have superuser access to the database.

Set up remote access

Submitted by sandip on Mon, 10/27/2008 - 21:16.

Add the below line to /var/lib/pgsql/data/postgresql.conf to make postgresql database listen to external connections:

listen_addresses = '*'

Edit /var/lib/pgsql/data/pg_hba.conf and add the appropriate permissions:

host all all 0.0.0.0/0 md5

change postgresql database owner

Submitted by sandip on Sat, 08/16/2008 - 00:58.

template1=# ALTER DATABASE <dbname> OWNER TO <dbuser>;

setup postgresql database to use passwords to connect

Submitted by sandip on Thu, 08/07/2008 - 09:07.

Edit "/var/lib/pgsql/data/pg_hba.conf" with:

local   all       all                          md5
host    all       all       127.0.0.1/32       md5

local - socket connection
host - tcp connection

To connect via socket:

$ psql -U <username> -d <dbname>

To connect via tcp:

$ psql -U <username> -h <hostname> -d <dbname>

password reset

Submitted by sandip on Thu, 08/07/2008 - 08:57.

template1=# ALTER USER <dbuser> WITH PASSWORD '<password>';

Viewing owner and permissions

Submitted by sandip on Wed, 08/06/2008 - 17:01.

\d -- view the owner
\dp -- view permissions

create user and grant permissions

Submitted by sandip on Wed, 08/06/2008 - 16:32.

Create db user and grant permission to create databases.

# adduser <dbuser>
# su - postgres
$ createuser -d -S -R <dbuser>

PostgreSQL QuickStart/Reference Commands...

Related Reading:

Comment viewing options