Categories: Hostripples Featured

PGSQL commands !!!

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 DATABASE

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

Fire 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

=================


Vishwajit Kale
Vishwajit Kale blazed onto the digital marketing scene back in 2015 and is the digital marketing strategist of Hostripples, a company that aims to provide affordable web hosting solutions. Vishwajit is experienced in digital and content marketing along with SEO. He's fond of writing technology blogs, traveling and reading.

Recent Posts

This Diwali, Give Your Website the Glow It Deserves — Host Smarter,Rank Higher

Every Diwali Brings New Light, New Beginnings, and New Opportunities In today’s digital world, your website is the Diya that…

3 days ago

What Are the Best WordPress Photo Gallery Plugins for 2026?

Photography is not just about taking pictures—it’s about presenting them beautifully. For photographers and creative bloggers, WordPress photo gallery plugins…

2 weeks ago

How Can You Prepare Your Ecommerce Site/blog for Festivals?

Festivals are more than cultural celebrations—they’re golden opportunities for e-commerce stores and bloggers to attract, engage, and convert audiences. During…

3 weeks ago

Shared Hosting: Migrating Your Site Seamlessly

If you’re new to web hosting, the idea of moving your website from one provider to another might sound intimidating.…

1 month ago

Top 15 WooCommerce Plugins to Power Your eCommerce Store in 2025

WooCommerce powers over 5 million online stores, and its true potential comes alive with plugins that enhance functionality. The right…

1 month ago