1. How to change PostgreSQL root user password ?
The root user for postgresql database is the user “postgres“, connect to the database “postgres” with user “postgres“, and by using alter command you can change the “postgres” user password as follow:
$ psql -U postgres -d postgres Password: (oldpassword) psql (9.4.8) Type "help" for help. postgres=# ALTER USER postgres WITH PASSWORD 'newpassword'; ALTER ROLE postgres=# \q
Now, reconnect again using the new password:
$ psql -U postgres -d postgres Password: (newpassword) psql (9.4.8) Type "help" for help
Changing the password for normal users is similar as changing the password of the root user “postgres“. 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 'newpassword';
2. How to check whether PostgreSQL server is up and running?
- For CentOS/RHEL 7
# systemctl status postgresql-9.4
● postgresql-9.4.service - PostgreSQL 9.4 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-9.4.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2016-08-09 14:53:37 EET; 5 days ago
Process: 2240 ExecStart=/usr/pgsql-9.4/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=0/SUCCESS)
Process: 2213 ExecStartPre=/usr/pgsql-9.4/bin/postgresql94-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 2348 (postgres)
CGroup: /system.slice/postgresql-9.4.service
├─2348 /usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data
├─3652 postgres: logger process
├─3902 postgres: checkpointer process
├─3903 postgres: writer process
├─3904 postgres: wal writer process
├─3905 postgres: autovacuum launcher process
└─3906 postgres: stats collector process
- Debian 8 “Jessie”, and Ubuntu 14.04, and higher
$ sudo systemctl status postgresql * postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled) Active: active (exited) since Wed 2016-08-10 14:10:48 SAST; 4 days ago Main PID: 805 (code=exited, status=0/SUCCESS) CGroup: /system.slice/postgresql.service
3. How to start, stop and restart PostgreSQL database?
- For CentOS/RHEL 7
# systemctl stop postgresql-9.4 # systemctl start postgresql-9.4 # systemctl restart postgresql-9.4
- Debian 8 “Jessie”, and Ubuntu 14.04, and higher
$ sudo systemctl start postgresql $ sudo systemctl stop postgresql $ sudo systemctl restart postgresql
4. How do I find out what version of PostgreSQL Is running?
Just connect to any database in postgresql server, the current version of postgresql will appear just after successful connection to the database as bellow:
$ psql -U postgres -d postgres psql (9.4.8) Type "help" for help.
Also, You can run the following select statement when you are connected to postgresql database server.
postgres=# select version (); version -------------------------------------------------------------------------------------------------------------- PostgreSQL 9.4.8 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit (1 row)
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 mimastech_user WITH password 'tmppassword'; CREATE ROLE
- Method 2: Creating the user in the shell prompt, with createuser command.
$ createuser -P mimastech_user Enter password for new role: Enter it again:
-P –> to assign a password to new role “user”
6. How to create a PostgreSQL Database ?
There are two methods in which you can create two databases.
- Method 1: Creating the database in the PSQL prompt, with createuser command.
# CREATE DATABASE mydb WITH OWNER mimastech_user; CREATE DATABASE
- Method 2: Creating the database in the shell prompt, with createdb command.
$ createdb -O mimastech_user mydb CREATE DATABASE
-O –> database user to own the new database.
7. How do I get a list of databases in a Postgresql database Server?
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --------------------------+----------------+----------+-------------+-------------+----------------------- core_production | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | mydb | mimastech_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres
8. How to Delete/Drop an existing PostgreSQL database ?
To delete a database you need it’s name, assume you do not know the name, we first will list all databases then will delete mydb database.
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges --------------------------+----------------+----------+-------------+-------------+----------------------- core_production | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | mydb | mimastech_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows) postgres=# 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?
# \dt List of relations Schema | Name | Type | Owner --------+-------------------------------+-------+---------- public | account_profiles | table | postgres public | accounts | table | postgres public | address_translations | table | postgres public | addresses | table | postgres
On an empty database, you’ll get “No relations found.” message for the above command.
11. How do I get a list of all the tables in a Postgresql database with table size and description?
# \dt+ List of relations Schema | Name | Type | Owner | Size | Description --------+-------------------------------+-------+----------+------------+------------- public | account_profiles | table | postgres | 27 MB | public | accounts | table | postgres | 44 MB | public | address_translations | table | postgres | 56 kB | public | addresses | table | postgres | 40 kB |
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 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 in each user home you use to connect to postgreSQL server, as shown below.
$ cat ~/.psql_history select version (); \q CREATE USER mimastech_user WITH password 'tmppassword'; \q \l \l drop database mydb \e \timing select * from pg_catalog.pg_attribute;
Summary
In this article we showed you the most common basic used postgresql command examples that you must know to handle the postgreql database. Simple commands to create and drop database with roles, list tables, change postgres user password, and manage postgresql service,getting help, etc…. .These commands facilitate your work when managing postgresql database.
I hope this article is good enough for you.
See you in other articles.
