How to Use PgAdmin III
pgAdmin is the feature rich Open Source administration and development platform for PostgreSQL.
You can easily
- Search, edit and view data
- Execute queries.
- Backup and restore database
More info https://www.pgadmin.org/docs/pgadmin3/1.22/
Note
- Before you edit table data and execute the queries, please backup the database.
- There is a significant performance increase in running pgAdmin from the same computer that the PostgreSQL database server is running on.
- An old CM version (6, 10.5), Check row counts of errors (Step 2.b)
- Open pgAdmin
- Start>All programs>PostgreSQL>pgAdmin or Search pgAdmin
- Double Click on the database>Enter password>OK
- View Database Table and Search Data
- Database>cm (Default) >Schemas>Public>Table>Right Click on a table(Ex. st_account)>View Data>View All Records>
- Check row counts of network_error, st_task_error, st_task_error_message and st_heartbeat.
- If you have an old CM version such as 6, 10.2, 10.5 and the size of the backup file is big, you need delete errors (Step 6C).
- Check ContentManager.log.
- Example. No row with the given identifier exists: com.scala.pojo.modules.media.MediaItemFile Error in ContentManagerlog.
- Media is duplicated in player’s inventory tab. Files that are not associated with player plan are being pushed added to their inventory Fix Version/s:12.00 (Kansas)
- Player Inventory page can not be opened.
- Example. No row with the given identifier exists: com.scala.pojo.modules.media.MediaItemFile Error in ContentManagerlog.
- Execute below query.
- delete from st_inventory_file
- where sf_plan_id not in (select sf_id from st_player_plan);
- Search Data Right click on the database>Search Objects
- You can search for almost any kind of objects in a database. (Can not serach by value.)
- Edit Table Values
- Normally you change table values in Content Manager. It is not recommended to change data directly.
- Select a cell and revise it. Click Refresh or Save on the left top side.
- Execute queries. Normally you do not need do this step but if you use an old CM version, you might be required.
- Before executing the queries. Stop Apache Tomcat service and Scala Transmission Server Service. Right Click on TaskBar>Task Manager>Open Services>Stop Apache Tomcat Services and Scala Transmission Server Service
- Backup the database.
- Select the database>SQL>insert queries
- For example if you have many errors (st_Heartbeat and st_network_error)
- TRUNCATE TABLE st_network_error CASCADE;
- Execute the query. Query>Execute
- Confirm the success
- Check Network_Error Refresh>Confirm 0 Row
- Start services (Tomcat and Scala Transmission).
- Check Primary Keys and Foreign Keys. Checking missing keys
- Database>Schema>Public>Tables>Constraints
- Getting a list of all Primary Keys and Foreign keys
- Execute below query.
SELECT conrelid::regclass AS "FK_Table"
,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), 14, position(')' in pg_get_constraintdef(c.oid))-14) END AS "FK_Column"
,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), position(' REFERENCES ' in pg_get_constraintdef(c.oid))+12, position('(' in substring(pg_get_constraintdef(c.oid), 14))-position(' REFERENCES ' in pg_get_constraintdef(c.oid))+1) END AS "PK_Table"
,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), position('(' in substring(pg_get_constraintdef(c.oid), 14))+14, position(')' in substring(pg_get_constraintdef(c.oid), position('(' in substring(pg_get_constraintdef(c.oid), 14))+14))-1) END AS "PK_Column"
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE contype IN ('f', 'p ')
AND pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %'
ORDER BY pg_get_constraintdef(c.oid), conrelid::regclass::text, contype DESC;
6.2 Copy the table in Data Output. Paste into an Excel spreadsheet.
Ctrl A (select All)
Ctrl C (Copy)