In this blog, I will how to install Postgres database in Ubuntu 14.04. There are few commands to create database, change password and database owner.
$ sudo apt-get install postgresql postgresql-contrib
Connect to Database
$sudo -i -u postgres $psql
To quite from Postgres
To logout from postgres user
Postgres has two types of authentication:
- IDENT/PEER authentication: By running the “psql” command as a UNIX user e.g.: sudo -u postgres psql.
- TCP authentication: by TCP/IP connection using PostgreSQL’s own managed username/password. For this you have to specify host with -h option e.g. psql -d postgres -U postgres -h localhost
When logged in as other user, if you do:
$ psql -d postgres -U postgres
psql: FATAL: Peer authentication failed for user “postgres”
That’s because you are trying to connect to Postgres as PEER authentication. Unless you are logged in as postgres user into system, you will get this user. You have to use -h parameter (for host name) to connect via TCP authentication.
Few useful commands
1. Create a user
$sudo -i -u postgres $createuser --interactive
You have to give username
2. Set a passwrd to user
$psql postgres=#\password <username>;
Enter new password
OR to change Password
postgres=#ALTER USER 'username WITH PASSWORD 'new_password'
3. Create database
4. Change database owner
$psql postgres=#ALTER DATABASE <database> OWNER TO <user>
4. Change user Role
$psql postgres=#ALTER USER 'username' SUPERUSER
Note on Prompt:
$ = you are logged as Postgres user
postgres=# you are connected to Postgres