1. How to check POstgresql version.
[root@BMDB-02 ~]# psql –version
psql (PostgreSQL) 8.1.11
=================
2. How to create postgreSQL USER ?
/usr/local/pgsql/bin/createuser skadam
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
=================
3. 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)
=================
4. Changing PostgreSQL USER password.
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’;
=================
5. How to create a PostgreSQL Database ?
CREATE DATABASE mydb WITH OWNER skadam;
CREATE DATABASEAn example of How to Create a Table in PostgreSQL
CREATE TABLE phonebook(phone VARCHAR(32), firstname VARCHAR(32), lastname VARCHAR(32), address VARCHAR(64));
=============
6. How do I get a list of databases in a Postgresql database ?
pba=> \l
How do I get a list of all the tables in a Postgresql database?
pba=> \d
To logout of PGSQL :
pba=> \q
=================
7. How do I get history of commands used for postgresql?
cat ~/.psql_history
================
8. How to extract output of database query in .xls or CSV file ?
Log in to database and then execute below /tmp is generally used but you can change the location as per your need.
\o /tmp/final_mysql.xls
OR :
\o /root/youroutputfile.csvFire the command of which you need output.
=================
9. Postgres database dump(backup) command:
pg_dump –column-inserts -h`hostname` -Upba pba > /db/database_dump.sql
=================
When you’re running a business that relies on website traffic and sales to succeed. Then you need to keep it…
Migration! Yes, this word is very big in the web hosting industry and it has its importance. Especially for businesses…
The server is the backbone of the web hosting industry and it acts like a HERO in the web hosting…
Imagine you are on holiday having a cup of tea and browsing your website or blog.Then, what next?You will get…
The web hosting industry is growing every minute, day, and year. It has many terminologies that are important to understand…