This article will show you how to use the pg_dump utility that is built-in to PostgreSQL to backup or dump the database to a file. This is a utility I use all the time to quickly make periodic backups of my databases. This tool has the ability to create both compressed and uncompressed dumps of… Read more
PostgreSQL
pgAdmin – PostgreSQL GUI Management Tool
If you are looking for a GUI management tool for PostgreSQL databases, look no further. pgAdmin III is a freeware utility for *nix and Windows operating systems that allows you to design and manage your databases. Using this tool, you can create, edit, delete tables and data for your databases. It supports an enormous amount… Read more
How to find (log) slow queries in PostgreSQL 8.x, 9.x
One of the most important factors when troubleshooting the performance of an application or website, is the performance of the database. Improper indexing or inefficient SQL queries can kill the performance of your application rendering it useless. It is amazing what a simple tweak or change to a SQL statement can do. I have seen… Read more
How to install PostgreSQL 8.4 on CentOS 5.5
If you have tried installing PostgreSQL database on CentOS 5.5 using yum, you will notice that you an old, outdated version! There are several ways to get the 8.4.x version of Postgres installed on this OS, but here is the quick and easy method! Installing PostgreSQL 8.4.x on CentOS 5.5 Download the PostgreSQL 8.4 repository… Read more
PostgreSQL: How to reset the pg_stat statistics tables?
The Postgres pg_stat tables show a variety of statistical information regarding the database. In certain situations (such as after major updates to your application), you may want to clear out the gathered statistics and start from scratch. For instance, if you recently implemented numerous SQL query and indexing optimizations, and you want to see statistical… Read more
PostgreSQL: How to reload config settings without restarting database
If you are making modifications to the Postgres configuration file postgresql.conf (or similar), and you want to new settings to take effect without needing to restart the entire database, there are two ways to accomplish this. Option 1: From the command-line shell su - postgres /usr/bin/pg_ctl reload Option 2: Using SQL SELECT pg_reload_conf(); Using either… Read more
How to view table/row locks in Postgres?
This article will show you how to see a list view a list of locks that are currently open (or waiting) on your database rows and tables. This information is beneficial when debugging timing-related errors and data inconsistencies. Login to the PostgresSQL command-line interface psql -U [username] [database_name] Run the following query: select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from… Read more
How to see active SQL queries and open connections in Postgres?
This article will show you how to see a list of open database connections as well as all active queries that are running on a PostgresSQL 8.x database. This information can be very beneficial when profiling your application and determining queries that have “gone wild” and are eating CPU cycles. Login to the PostgresSQL command-line… Read more
How to find when Postgres tables were auto-vacuumed and auto-analyzed?
This article will show you how to determine when your database tables were last vacuumed, auto-vacuumed, analyzed, and auto-analyzed on a PostgresSQL 8.x database. Login to the PostgresSQL command-line interface psql -U [username] [database_name] Run the following query: select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables… Read more
How to delete/drop a constraint in PostgresSQL
This article will show you how to drop a constraint, such as a foreign key constraint, on a PostgresSQL 8.x database. Login to the PostgresSQL command-line interface psql -U [username] [database_name] Show a list of constraints on a particular table \d [table_name] To Delete (or Drop) the constraint, use the command: ALTER TABLE [table_name] DROP… Read more
