Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL installation,users ,databases ...

 We have list some basic commands like installing PostgreSQL, creating users and databases for PostgreSQL, etc. CRUD with PostgreSQL is not the focus of this post. All the commands are tested in Ubuntu 14.04 LTS and Ubuntu 16.04 LTS. There may be some other richer ways to perform the operations mentioned in this post, but my focus was to keep the stuff simple with minimal complexity. I have used the terms ‘user’ and ‘role’ interchangeably in the blog, as they are analogous with respect to PostgreSQL.
Install PostgreSQL:
sudo apt-get update
sudo apt-get install postgresql -y
Check PostgreSQL version:
psql --version
Log into PostgreSQL. Login as default user i.e. postgres with password password_of_your_ubuntu_system:
sudo -u postgres psql
Create a new user for PostgreSQL and assign a password to it. Since it is not considered a good practice to persist your data in PostgreSQL through the default user i.e. postgres, we will create a new user and assign a password to it. It can be done by 2 possible ways:
First way:
sudo -u postgres createuser --interactive
Here, you would be asked name of the role/user, if you want to assign superuser privileges to the new user or not; and if not whether the user be allowed to create databases and new roles.
After creating the user, login to PostgreSQL console via the default user i.e. postgres and then alter the password for the user via following command (assuming the new user/role you created is lihas):
Note: In case the name of your user contains capital letters, wrap the username into double quotes while performing all user related operations like:
Second way:
Log into PostgreSQL console via the default user ie ‘postgres’ and then create the user:
CREATE USER lihas WITH PASSWORD 'lihas'; --Assuming the new user/role we want to create is lihas
ALTER USER lihas WITH CREATEDB; --user lihas con create databases
ALTER USER lihas WITH CREATEUSER; --user lihas con create new users/roles
Note: According to the convention, whenever a new user is created; a database with the same name as the new username must be created and this database shall not be used to store data. You may create the database by following command 10 below.
List all users in PostgreSQL:
Switch to a user:
SET ROLE user_name;
Check current user:
Delete a user/role:
DROP USER user_name;
List all databases:
Create database:
By default, the owner of any database you create is postgres, if you want your database to belong to a specific user, switch to that user (see above) and then create the database.
Enter a database. Enter inside database via the default user:
\c database_name
\connect database_name
Enter inside the database with a specific user:
\c database_name user_name
If the above command does not work (Peer authentication failed), simply change the database first and then switch to the user (see command 6 above).
Note: You may also log in directly into a database with a certain user from the terminal by following command (with password as password_of_your_ubuntu_system).
sudo -u role_name psql db_name
But for this command to work, role_name must be a valid Linux user name. You may add a user to Linux by following :
sudo adduser role_name;
Drop database:
List all tables:
Describe schema of a table:
\d table_name
Exit out of PostgreSQL:
Hope this compilation helps you. Share your thoughts in the comments below.


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL Introduction