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)

1. Open pgAdmin
   1. Start>All programs>PostgreSQL>pgAdmin or Search pgAdmin

1. View Database Table and Search Data
   1. Database>cm (Default) >Schemas>Public>Table>Right Click on a table(Ex. st_account)>View Data>View All Records>

1. Check ContentManager.log.
   1. 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.

• Execute below query.
• delete from st_inventory_file
• where sf_plan_id not in (select sf_id from st_player_plan);

1. 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.)

1. 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.

1. Execute queries. Normally you do not need do this step but if you use an old CM version, you might be required.
   1. 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

1. Start services (Tomcat and Scala Transmission).
1. Check Primary Keys and Foreign Keys. Checking missing keys
   1. Database>Schema>Public>Tables>Constraints

1. 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)