Home » Blogs » sandip's blog

PostgreSQL QuickStart/Reference Commands...

Sat, 04/15/2006 - 19:47 — sandip

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:

Bookmark/Search this post with
  • del.icio.us
  • Digg
  • Facebook
  • identi.ca
  • LinkedIn
  • SlashDot
  • StumbleUpon
  • Technorati
  • Twitter

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Wed, 10/12/2011 - 15:05 — sandip postgresql development tips

plpgsql development tips

Tue, 06/14/2011 - 16:01 — sandip 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;

Wed, 02/16/2011 - 19:16 — sandip 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;

Wed, 02/16/2011 - 17:03 — sandip 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

Wed, 02/16/2011 - 13:03 — sandip 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

Wed, 02/16/2011 - 12:52 — sandip show all variables

show all;

Mon, 02/07/2011 - 13:07 — sandip 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

Wed, 04/01/2009 - 23:12 — sandip 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.

Fri, 11/14/2008 - 12:03 — Anonymous 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.

Mon, 10/27/2008 - 21:16 — sandip 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

Sat, 08/16/2008 - 00:58 — sandip change postgresql database owner

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

Thu, 08/07/2008 - 09:07 — sandip setup postgresql database to use passwords to connect

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

local   all    &nbsp;  all    &nbsp;    &nbsp;    &nbsp;    &nbsp;    &nbsp; md5
host    all    &nbsp;  all    &nbsp;  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>

Thu, 08/07/2008 - 08:57 — sandip password reset

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

Wed, 08/06/2008 - 17:01 — sandip Viewing owner and permissions

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

Wed, 08/06/2008 - 16:32 — sandip create user and grant permissions

Create db user and grant permission to create databases.

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

Comment