1. 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)
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';
2. How to setup PostgreSQL SysV startup script?$ su - root
# tar xvfz postgresql-8.3.7.tar.gz
# cd postgresql-8.3.7
# cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql
# chmod a+x /etc/rc.d/init.d/postgresql
3. How to check whether PostgreSQL server is up and running?$
/etc/init.d/postgresql status
Password:
pg_ctl: server is running (PID: 6171)
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"
[
Note: The status above indicates the server is up and running]
$
/etc/init.d/postgresql status
Password:
pg_ctl: no server running
[
Note: The status above indicates the server is down]
4. How to start, stop and restart PostgreSQL database?#
service postgresql stop
Stopping PostgreSQL: server stopped
ok
#
service postgresql start
Starting PostgreSQL: ok
#
service postgresql restart
Restarting PostgreSQL: server stopped
ok
5. How do I find out what version of PostgreSQL I am running?$
/usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=#
select version();
version
----------------------------------------------------------------------------------------------------
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)
test=#
5. How to create a PostgreSQL user ?
There are two methods in which you can create user.
Method 1: Creating the user in the PSQL prompt, with CREATE USER command.
#
CREATE USER ramesh WITH password 'tmppassword';
CREATE ROLE
Method 2: Creating the user in the shell prompt, with createuser command.
$
/usr/local/pgsql/bin/createuser sathiya
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
6. How to create a PostgreSQL Database ?
There are two metods in which you can create two databases.
Method 1: Creating the database in the PSQL prompt, with createuser command.
