PostgreSQL QuickStart/Reference Commands...

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:

  1. Login as "postgres" (SuperUser) to start using database:
    # su - postgres
    
  2. Create a new database:
    $ createdb mydb
    
  3. Drop database:
    $ dropdb mydb
    
  4. Access database:
    $ psql mydb
    
  5. Get help:
    mydb=# \h
    
  6. Quit:
    mydb=# \q
    
  7. Read command from file:
    mydb=# \i input.sql
    
  8. To dump a database:
    $ pg_dump mydb > db.out
    
  9. To reload the database:
    $ psql -d database -f db.out
    
  10. Dump all database:
    # su - postgres
    # pg_dumpall > /var/lib/pgsql/backups/dumpall.sql
    
  11. Restore database:
    # su - postgres
    # psql -f /var/lib/pgsql/backups/dumpall.sql mydb
    
  12. Show databases:
    #psql -l
    or
    mydb=# \l;
    
  13. Show users:
    mydb=# SELECT * FROM "pg_user";
    
  14. Show tables:
    mydb=# SELECT * FROM "pg_tables";
    
  15. Set password:
    mydb=# UPDATE pg_shadow SET passwd = 'new_password' where usename = 'username';
    
  16. Clean all databases (Should be done via a daily cron):
    $ vacuumdb --quiet --all
    

Related Reading:

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

postgresql development tips

Buffer hit cache ratio

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

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;

autovacuum daemon

To enable autovacuum, stats_start_collector and stats_row_level must be enabled in "postgresql.conf":

autovacuum = on
stats_start_collector = on
stats_row_level = on

Reference: autovacuum

list queries running

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

show all;

List database size

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

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

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

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

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

setup postgresql database to use passwords to connect

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

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

Viewing owner and permissions

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

create user and grant permissions

Create db user and grant permission to create databases.

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

Comment