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?
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 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;
Comments on this entry are closed.
It is very simple but more useful. thanks.
very useful for beginners like me. Thanks.
i get list of the tables ,and highlighted
i cannot enter any text or command. How to exit the description and input next commands?
thanks! nice guide!
\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.
this is very useful for me
As a SQLServer User I did not know where to start with PostGre SQL, so this article was extremely useful! Thank you so much 🙂
very good doc
nice one..thanks lot
You ROCK \m/
THX for this information!
Karel, you need to press ‘q’ which will quit the screen that you are seeing and allow you to input further Postgres commands.
very nice.very useful,thanks
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.
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.
\dv will list out all the views. How can we see the body of particular view in putty??
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.
We can change the password using passwd (Newpassword)
I am using Windows 7 pro and working with Wideorbit’s Radio Automation system. I am getting this message: “DB Service pqsql -8.1 is not running!
Waiting for the DB service.” when I try to run the automation system. How do I fix this problem?