Shulou(Shulou.com)06/01 Report--

Since all the relevant report data (application running log, call list, session list) and configuration information of AEP EPM are stored on the local PostgreSQL, understanding the relevant basic usage of PostgreSQL will help to improve the daily operation and maintenance ability. This article mainly summarizes how to turn on local login, remote login, basic commands, data backup and cleaning.

How to enable local and remote login

During EPM installation, PostgreSQL is also installed, prompting for the username postgres password and creating a report user. At that time, when you use PostgreSQL locally to log in to the database, you can't log in all the time, and you can't log in through the PostgreSQL client, so you need to do the following to open local and remote login.

[root@vp142 VP-Tools] # su-postgres-bash-4.1 $ls9.0 data pgstartup.log SQLscripts-bash-4.1$ cd data/-bash-4.1$ vi pg_hba.conf / / find the following section, modify the first record (run local login) and add a new record (run remote login, remember to back up the configuration file first)

After modification: save by wq, and then restart the PostgreSQL service.

-bash-4.1$ exitlogout [root@vp142 VP-Tools] # service postgresql restart

Local and remote login verification

[root@vp142 VP-Tools] # psql-h 127.0.0.1-U postgres-d VoicePortalPassword for user postgres: psql (9.0.15) Type "help" for help.VoicePortal=# / / Local login succeeded

Remote login: download PostgreSQL client, configure

Login succeeded:

PostgreSQL common commands

VoicePortal-#\ l / / output all databases List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges-+- +-- VoicePortal | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | = c/postgres VoicePortal-#\ c postgres / / switch to postgres library You are now connected to database "postgres". Postgres-# VoicePortal-#\ d / / shows which tables are available in the current library List of relations Schema | Name | Type | Owner-+-+-public | alarmcode | table | postgres public | alarmcodelistenerlink | table | postgres public | alarmcodelistenerlinkdefault | table | postgres public | alarmhistory | table | postgres public | alarmlistener | table | postgres public | alarmnotify | table | postgres. VoicePortal-#\ d cdr / / View the structure of cdr table Table "public.cdr" Column | Type | Modifiers-+- -+-calltimestamp | timestamp without time zone | recordid | integer | sessionid | character varying | callid | | character varying | ucid | character varying | portid | integer | create database: create database [database name] | Delete database: drop database [database name]; * rename a table: alter table [table name A] rename to [table name B]; * delete a table: drop table [table name]; * add fields to an existing table: alter table [table name] add column [field name] [type]; * delete fields in a table: alter table [table name] drop column [field name] * rename a field: alter table [table name] rename column [field name A] to [field name B]; * set a default value for a field: alter table [table name] alter column [field name] set default [new default value]; * remove the default value: alter table [table name] alter column [field name] drop default; insert data in the table: insert into table name ([field name m], [field name n],.) Values ([value of column m], [value of column n],.); modify the data of a row and column in the table: update [table name] set [target field name] = [target value] where [row characteristic]; delete a row of data in the table: delete from [table name] where [row characteristic]; delete from [table name];-- delete the entire table to create a table: create table ([field name 1] [type 1]) , [field name 2] [type 2],.) \ copyright displays the terms of use and release of PostgreSQL\ encoding [character Encoding name] displays or sets client character encoding\ h [name] syntactic description of the SQL command Prompt the user to set the internal variable\ password [USERNAME] securely change the password for a user\ Q to exit psql with the * display all commands\ prompt [text] name

Database backup and recovery

PostgreSQL data backup: [root@vp142 VP-Tools] # pg_dump-U postgres VoicePortal > / cpic/craft/postgresdata.20160425.sqlPassword: / / enter the password and wait for the backup to complete. [root@vp142 VP-Tools] # ll / cpic/craft/postgresdata.20160425.sql / / View backup files-rw-r--r-- 1 root root 4007564 Apr 25 16:57 / cpic/craft/postgresdata.20160425.sqlPostgreSQL data recovery: clear the database first (this script clears the relevant report data Not important configuration information of the system): [root@vp142 VP-Tools] # bash PurgeReportDataLocalDB Do you wish to purge all your report data?Press enter to continue Or press control-C to abort this utilityPurging SDR table...Purging CDR table...Purging VPAppLog table...Purging VPPerformance table...Purging completed U postgres VoicePortal-start data recovery: [root@vp142 VP-Tools] # psql-U postgres VoicePortal < / cpic/craft/postgresdata.20160425.sqlPassword For user postgres: lowrite-535 (1 row) lo_close-0 (1 row) COMMIT.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.