15实用的PostgreSQL数据库管理命令

概述15 Practical PostgreSQL Database Administration Commands 1. How to change PostgreSQL root user password ? $ /usr/local/pgsql/bin/psql postgres postgresPassword: (oldpassword)# ALTER USER postgres WI 15 Practical Postgresql Database administration Commands 1. How to change Postgresql root user password ?

$ /usr/local/pgsql/bin/psql postgres postgresPassword: (oldpassword)# ALTER USER postgres WITH PASSWORD 'tmppassword';$ /usr/local/pgsql/bin/psql postgres postgresPassword: (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 statusPassword: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 statusPassword: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 stopStopPing Postgresql: server stoppedok# service postgresql startStarting Postgresql: ok# service postgresql restartRestarting Postgresql: server stoppedok

5. How do I find out what version of Postgresql I am running?

$ /usr/local/pgsql/bin/psql testWelcome 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 quittest=# 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 sathiyaShall the new role be a superuser? (y/n) nShall the new role be allowed to create databases? (y/n) nShall the new role be allowed to create more new roles? (y/n) nCREATE 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,17)"># 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 rameshCREATE 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 databasesname | Owner | EnCoding----------+----------+----------backup | postgres | UTF8mydb | ramesh | UTF8postgres | postgres | UTF8template0 | postgres | UTF8template1 | postgres | UTF8

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

# \lList of databasesname | Owner | EnCoding----------+----------+----------backup | postgres | UTF8mydb | ramesh | UTF8postgres | postgres | UTF8template0 | postgres | UTF8template1 | 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.

# \timingTiming is on.# SELECT * from pg_catalog.pg_attribute ;Time: 9.583 ms

12. How To Backup and Restore Postgresql Database and table?

We discussed earlIErhow 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_historyalter user postgres with password 'tmppassword';\h alter userselect version();create user ramesh with password 'tmppassword';\timingselect * from pg_catalog.pg_attribute;

总结

以上是内存溢出为你收集整理的15实用的PostgreSQL数据库管理命令全部内容，希望文章能够帮你解决15实用的PostgreSQL数据库管理命令所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。