15 Practical PostgreSQL Database Administration Commands

by Ramesh Natarajan on April 16, 2009

PostgreSQL DBA Commands
Earlier we discussed about how to install PostgreSQL database on Linux from source.

In this article, let us review top 15 practical postgreSQL DBA command examples.

If you are a mySQL administrator, check-out our 15 examples of mysqladmin command article that we discussed a while back.


1. How to change PostgreSQL root user password ?

$ /usr/local/pgsql/bin/psql postgres postgres
Password: (oldpassword)
# ALTER USER postgres WITH PASSWORD 'tmppassword';

$ /usr/local/pgsql/bin/psql postgres postgres
Password: (tmppassword)


Changing the password for a normal postgres user is similar as changing the password of the root user. Root user can change the password of any user, and the normal users can only change their passwords as Unix way of doing.

# ALTER USER username WITH PASSWORD 'tmppassword';

2. How to setup PostgreSQL SysV startup script?

$ su - root

# tar xvfz postgresql-8.3.7.tar.gz

# cd postgresql-8.3.7

# cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql

# chmod a+x /etc/rc.d/init.d/postgresql

3. How to check whether PostgreSQL server is up and running?

$ /etc/init.d/postgresql status
Password:
pg_ctl: server is running (PID: 6171)
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"
[Note: The status above indicates the server is up and running]

$ /etc/init.d/postgresql status
Password:
pg_ctl: no server running
[Note: The status above indicates the server is down]

4. How to start, stop and restart PostgreSQL database?

# service postgresql stop
Stopping PostgreSQL: server stopped
ok

# service postgresql start
Starting PostgreSQL: ok

# service postgresql restart
Restarting PostgreSQL: server stopped
ok

5. How do I find out what version of PostgreSQL I am running?

$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=# select version();
version
----------------------------------------------------------------------------------------------------
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)

test=#

5. How to create a PostgreSQL user ?

There are two methods in which you can create user.

Method 1: Creating the user in the PSQL prompt, with CREATE USER command.

# CREATE USER ramesh WITH password 'tmppassword';
CREATE ROLE

Method 2: Creating the user in the shell prompt, with createuser command.

$ /usr/local/pgsql/bin/createuser sathiya
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE

6. How to create a PostgreSQL Database ?

There are two metods in which you can create two databases.

Method 1: Creating the database in the PSQL prompt, with createuser command.

# CREATE DATABASE mydb WITH OWNER ramesh;
CREATE DATABASE

Method 2: Creating the database in the shell prompt, with createdb command.

$ /usr/local/pgsql/bin/createdb mydb -O ramesh
CREATE DATABASE

* -O owner name is the option in the command line.

7. How do I get a list of databases in a Postgresql database ?

# \l  [Note: This is backslash followed by lower-case L]
List of databases
Name | Owner | Encoding
----------+----------+----------
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

8. How to Delete/Drop an existing PostgreSQL database ?

# \l
List of databases
Name | Owner | Encoding
----------+----------+----------
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

# DROP DATABASE mydb;
DROP DATABASE

9. Getting help on postgreSQL commands

\? will show PSQL command prompt help. \h CREATE will shows help about all the commands that starts with CREATE, when you want something specific such as help for creating index, then you need to give CREATE INDEX.

# \?

# \h CREATE

# \h CREATE INDEX

10. How do I get a list of all the tables in a Postgresql database?

# \d

On an empty database, you’ll get “No relations found.” message for the above command.

11. How to turn on timing, and checking how much time a query takes to execute ?

# \timing — After this if you execute a query it will show how much time it took for doing it.

# \timing
Timing is on.

# SELECT * from pg_catalog.pg_attribute ;
Time: 9.583 ms

12. How To Backup and Restore PostgreSQL Database and Table?

We discussed earlier how to backup and restore postgres database and tables using pg_dump and psql utility.

13. How to see the list of available functions in PostgreSQL ?

To get to know more about the functions, say \df+

# \df

# \df+

14. How to edit PostgreSQL queries in your favorite editor ?

# \e

\e will open the editor, where you can edit the queries and save it. By doing so the query will get executed.

15. Where can i find the postgreSQL history file ?

Similar to the Linux ~/.bash_history file, postgreSQL stores all the sql command that was executed in a history filed called ~/.psql_history as shown below.

$ cat ~/.psql_history
alter user postgres with password 'tmppassword';
\h alter user
select version();
create user ramesh with password 'tmppassword';
\timing
select * from pg_catalog.pg_attribute;

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 16 comments… read them below or add one }

1 sasikala April 16, 2009 at 10:20 pm

It is very simple but more useful. thanks.

2 mike March 21, 2010 at 10:41 pm

very useful for beginners like me. Thanks.

3 karel April 28, 2010 at 2:59 am

after using
\d
i get list of the tables ,and highlighted
(END)

i cannot enter any text or command. How to exit the description and input next commands?

4 markitus82 May 18, 2010 at 10:03 am

thanks! nice guide!

5 NR August 11, 2010 at 8:54 pm

Some clarifications:
\d gaves you more then just tables, to see only tables use \dt. To see views use \dv, to see a table or any other object’s description use \d objectname. You can use \? to see a list of commands and \? commandname to get help with a command. The same way you can use \h to see a list of SQL commands and \h sqlcommand to get help with a SQL command.

Be aware that a simple create database command will create a database with default parameters, this is not always what we want or need. Usually it is ok for english speakers but other languages migth need a different collating order or even encoding, as the default postgresql installation does not (or did not) use UTF-8. To change this and other parameters in a safe and permanent way you migth need to create a all new database cluster as some parameters cannot be changed after database creation and others have to be common to all databases (see http://www.postgresql.org/docs/8.4/interactive/locale.html for postgreSQL 8.4).

Has for the Karel’s problem, simply use ‘q’ to quit the viewing and came back to the prompt.

6 Anonymous September 20, 2011 at 11:50 pm

this is very useful for me

7 Anonymous July 24, 2012 at 7:26 am

As a SQLServer User I did not know where to start with PostGre SQL, so this article was extremely useful! Thank you so much :-)

8 sriram November 28, 2012 at 4:08 am

very good doc

9 Anonymous February 15, 2013 at 4:55 am

nice one..thanks lot

10 Cesar February 23, 2013 at 11:10 pm

You ROCK \m/

THX for this information!

11 Ryan March 10, 2013 at 3:40 pm

Karel, you need to press ‘q’ which will quit the screen that you are seeing and allow you to input further Postgres commands.

12 sindhuja August 9, 2013 at 11:45 pm

very nice.very useful,thanks

13 srivani September 3, 2013 at 7:45 am

Hi,

Thanks for the useful doc.
I have f3 functions with same name different parameters in PostgreSQL, I need to check the so I need to check the source based on their parameters, could you please tell me what is the command to see source code of function based on my choice of parameters.
Thanks.

14 alfred September 5, 2013 at 5:45 am

Hi am very new postgresql.Recently i joined as a postgresql DBA. So what are my daily responsiblites. And how can i take care those resposiblites. Please suggest me and also send me some useful links regaring that. Thanks advance.

15 tarun August 20, 2014 at 2:47 am

\dv will list out all the views. How can we see the body of particular view in putty??

16 Deepak September 15, 2014 at 5:32 am

Hello, in Postgres 9.3.5 on Ubuntu 14.04, the command ‘service postgres(ql) xxx’ does not work. So, whats the alternative? Kindly reply. Thanks.

Leave a Comment

Previous post:

Next post: