Earlier we discussed about how to install PostgreSQL database on Linux from source.
In this article, let us review top 15 practicalpostgreSQL DBA command examples.
If you are a mySQL administrator, check-out our 15 examples of mysqladmin commandarticle 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;
