//
you're reading...
Uncategorized

Postgres Administrator

Show All Query/Connection Actvity :

    1. Login to the PostgresSQL command-line interface

psql -U [username] [database_name]

    1. Run the following query:

SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity;

Or Using PgAdmin III

  1. Open up pgAdmin
  2. Connect to your server
  3. Select Tools -> Server Status

server_status1.jpg

Most Postges Command :

 

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.

# CREATE DATABASE mydb WITH OWNER ramesh;
CREATE DATABASE

Method 2: Creating the database in the shell prompt, with createdb command.

$ /usr/local/pgsql/bin/createdb mydb -O ramesh
CREATE DATABASE

* -O owner name is the option in the command line.

7. How do I get a list of databases in a Postgresql database ?

# \l  [Note: This is backslash followed by lower-case L]
List of databases
Name | Owner | Encoding
----------+----------+----------
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

8. How to Delete/Drop an existing PostgreSQL database ?

# \l
List of databases
Name | Owner | Encoding
----------+----------+----------
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

# DROP DATABASE mydb;
DROP DATABASE

9. Getting help on postgreSQL commands

\? will show PSQL command prompt help. \h CREATE will shows help about all the commands that starts with CREATE, when you want something specific such as help for creating index, then you need to give CREATE INDEX.

# \?

# \h CREATE

# \h CREATE INDEX

10. How do I get a list of all the tables in a Postgresql database?

# \d

On an empty database, you’ll get “No relations found.” message for the above command.

11. How to turn on timing, and checking how much time a query takes to execute ?

# \timing — After this if you execute a query it will show how much time it took for doing it.

# \timing
Timing is on.

# SELECT * from pg_catalog.pg_attribute ;
Time: 9.583 ms

12. How To Backup and Restore PostgreSQL Database and Table?

We discussed earlier how to backup and restore postgres database and tables using pg_dump and psql utility.

13. How to see the list of available functions in PostgreSQL ?

To get to know more about the functions, say \df+

# \df

# \df+

14. How to edit PostgreSQL queries in your favorite editor ?

# \e

\e will open the editor, where you can edit the queries and save it. By doing so the query will get executed.

15. Where can i find the postgreSQL history file ?

Similar to the Linux ~/.bash_history file, postgreSQL stores all the sql command that was executed in a history filed called ~/.psql_history as shown below.

$ cat ~/.psql_history
alter user postgres with password 'tmppassword';
\h alter user
select version();
create user ramesh with password 'tmppassword';
\timing
select * from pg_catalog.pg_attribute;

 

 

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

July 2016
M T W T F S S
« Oct    
 123
45678910
11121314151617
18192021222324
25262728293031

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: