AVAYA AEP Koriyuki PostgreSQL Database related

Since all relevant report data of AEP EPM (Application run log, call list, session list), configuration information, etc. exist on the local PostgreSQL, understand the basic usage of PostgreSQL and help to improve the daily operation and maintenance ability. This article mainly summarizes how to open a local login, remote login, basic commands, data backup and cleanup.

• How to open local and remote logins

During the EPM installation, PostgreSQL is also installed, prompting for the user name Postgres password and creating a report user. At that time, when you use PostgreSQL to log in to the database, always landing, through the PostgreSQL client also always landing, you need to do the following to open local and remote login.

[[email protected] VP-Tools] # su-postgres
-bash-4.1 $ ls
9.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 record (run remote login, remember to back up the configuration file first)

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/7F/69/wKioL1cd1qqD1Sd-AADRaBNkM5s660.jpg "title=" 1.jpg " alt= "Wkiol1cd1qqd1sd-aadrabnkm5s660.jpg"/>

After the change: Wq save and restart the PostgreSQL service.

-bash-4.1$ exit
logout
[[email protected] VP-Tools]# service postgresql restart

• Local and remote login verification

[[email protected] VP-Tools] # psql -h 127.0.0.1 -U postgres -d VoicePortal
Password for user postgres:
psql (9.0.15)
Type "help" for help.

VoicePortal = # // Local login successful

Remote login: Download PostgreSQL client, configure

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/7F/6B/wKiom1cd2IDR_lQEAABctO_taDo748.jpg "style=" float: none; "title=" 2.jpg "alt=" Wkiom1cd2idr_lqeaabcto_tado748.jpg "/>

Login success:

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/7F/69/wKioL1cd2UjAFC2hAADLYhEcnLs896.jpg "style=" float: none; "title=" 3.jpg "alt=" Wkiol1cd2ujafc2haadlyhecnls896.jpg "/>

• PostgreSQL Common Commands

VoicePortal- # \ l // Output all databases
                                   List of databases
    Name | Owner | Encoding | Collation | Ctype | Access privil
eges
------------- + ---------- + ---------- + ------------- + ------------- + ----------------
-------
 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 the postgres library
You are now connected to database "postgres".
postgres- #
VoicePortal- # \ d // Show which tables the current library has
                      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 the cdr table
                                            Table "public.cdr"
       Column | Type | Modifi
ers
-------------------- + ----------------------------- + -----------------------------
---------------------------
 calltimestamp | timestamp without time zone |
 recordid | integer |
 sessionid | character varying |
 callid | character varying |
 ucid | character varying |
 portid | integer |
 
 Create the database:
create database [database name];
Delete the 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 existing tables:
alter table [table name] add column [field name] [type];
* Delete fields in the 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 default values:
alter table [table name] alter column [field name] drop default;
Insert data into 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 a column in the table:
update [table name] set [target field name] = [target value] where [characteristics of the row];
Delete a row of data in the table:
delete from [table name] where [characteristics of the row];
delete from [table name];-Delete the entire table
Create the table:
create table ([field name 1] [type 1];, [field name 2] [type 2], ... <, primary key (field name m, field name n, ...)>;) ;
\ copyright shows PostgreSQL terms of use and distribution
\ encoding [character encoding name]
                 Display or set client character encoding
\ h [name] Explanation of SQL command syntax, use * to display all commands
\ prompt [text] name
                 Prompt user to set internal variables
\ password [USERNAME]
                 securely change the password for a user
\ q Quit psql

• Database backup and Recovery

PostgreSQL data backup:
[[email protected] VP-Tools] # pg_dump -U postgres VoicePortal> / cpic / craft / postgresdata
.20160425.sql
Password: // After entering the password, wait for the backup to complete.
[[email protected] VP-Tools] # ll /cpic/craft/postgresdata.20160425.sql // View backup file
-rw-r--r-- 1 root root 4007564 Apr 25 16:57 /cpic/craft/postgresdata.20160425.sql

PostgreSQL data recovery:
First empty the database:
[[email protected] VP-Tools] # bash PurgeReportDataLocalDB

Do you wish to purge all your report data?

Press enter to continue, or press control-C to abort this utility

Purging SDR table ...
Purging CDR table ...
Purging VPAppLog table ...
Purging VPPerformance table ...
Purging completed!
-------------------------------------------------- ---
Start recovering data:
[[email protected] VP-Tools] # psql -U postgres VoicePortal </cpic/craft/postgresdata.20160425.sql
Password for user postgres:
 lowrite
---------
     535
(1 row)

 lo_close
----------
        0
(1 row)

COMMIT
. . . . . . 

This article is from the "Reminder Flower Rain" blog, please make sure to keep this source http://chenwen.blog.51cto.com/771416/1767577

AVAYA AEP Koriyuki PostgreSQL Database related