| POSTGRESQL COMMANDS Purpose Command Login to postgresql : psql -d dbname -U usernameConnect to other DB: /connect dbname Describe table Structure: \d + tablename Show all tables: \d Show all databases : \l Create new database: create database dbname Show any table rows: select * from table name; Change user password: ALTER USER un WITH PASSWORD 'mypass' ; Create new user: create user usrnm with password 'password'; Version: select version() Delete database: DROP DATABASE databasename; Help for any command: \? |
Postgres Installtion Steps:
step1-
yum install postgresql postgresql-server
step2-
# initialize the database first.....!!
[root@localhost lib]# service postgresql initdb
Initializing database: [ OK ]
step3-
On the services with chkconfig command
[root@localhost lib]# chkconfig postgresql on
[root@localhost lib]#
step4-
Start the database---
[root@localhost lib]# service postgresql start
Starting postgresql service: [ OK ]
step5-
Create the admindb like below
su - postgres
createdb admindb
and now alter the postgres user with password like below
# psql -U postgres (it will directly connect to database)
> ALTER USER postgres WITH PASSWORD '<user_password>';
step6-
To connect to DB from linux terminal, We need to made the below changes in the file.
>cd /var/lib/pgsql/9.2/data/;ll
>vi pg_hba.conf
Changes the below entries in the file
local all all ident ## Change to md5
# IPv4 local connections:
host all all 127.0.0.1/32 ident ## Change to md5
# IPv6 local connections:
host all all ::1/128 ident ## Change to md5
step7-
Restart the postgres
> service postgresql restart
Now connect to DB from linux console directly
[root@localhost data]# psql -U postgres
Password for user postgres: # Give password as password
Errors while installing postgresql Database :
---->
[root@localhost data]# su - postgres
-bash: TMOUT: readonly variable
-bash-4.1$ createdb admindb
createdb: could not connect to database template1: FATAL: could not open relation mapping file "global/pg_filenode.map": No such file or directory
sol:
To rectify the above error, check for postmaster file ( ps -ef grep post )
kill the pid of the process ( postmaster file) and then restart the postgresql DB
---->
If any Fatal error get then kill all the java sessions and postgres sessions and restart the postgres DB then it will work: :)