15实用的PostgreSQL数据库管理命令
15 Practical PostgreSQL Database Administration Commands1. 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) # 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,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 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 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_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;
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |