16 PostgreSQL Database Administration Commands

16 PostgreSQL Database Administration Commands

Following are basic PostgreSQL database administration commands which each PostgreSQL database administrator should know. These PostgreSQL database administration commands include creating users in PostgreSQL, setting up user credentials in PostgreSQL, change / update PostgreSQL user password, check whether PostgreSQL is up and running, commands to create, delete, drop, start, stop, restart, backup, restore PostgreSQL database, getting the list of all databases in PostgreSQL, finding out what version of PostgreSQL is running, PostgreSQL help and history commands, commands to get the list of all the tables in a PostgreSQL database, commands to turn on timing and checking how much time a query takes to execute, commands to see the list of available functions in PostgreSQL etc. Lets have a look on following PostgreSQL Database Administration Commands.

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=#

6. 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

7. 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.

8. 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

9. 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

10. 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

11. 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.

12. 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

13. 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.

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

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

# \df

# \df+

15. 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.

16. 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;