How to run SQL script against a Postgresql Database ... - Randr Inc

arizonahoopleData Management

Nov 28, 2012 (5 years and 7 months ago)


How to run SQL script against a Postgresql Database
Step 1 – create a connection to your database
Click File, Add Server
You will get this pop up to define your server.
Step 2 – connect to your database
Open pgAdmin.
Right click on the database and select Connect
How_to_run_database_update_scripts.doc Page 1
Step 3 – Open your database
Once you have connected to your server you will see these options.
Open databases by clicking the +.
Then open your database by click the +.
Step 4 – open the Query Tool
The connection to your database will now be open.
Click on Schemas, public and Tables, you can see each individual table.
Go to the Menu at the top of the screen and click Tools, and select Query Tool. If that is unavailable,
click on Tables or one of the tables so you are positioned to a database
This will open a new window where you can run queries.
How_to_run_database_update_scripts.doc Page 2
Step 5 – Run the query
The top section is where you run your query. The bottom section is where it displays the results.
Paste in your sql statements in the top section or enter them. Click on the green triangle to run the
query/sql statements. The green triangle with the blue diskette allows you to save the query results into
a csv file.
If there is an error in your statements or query it will highlight the error.
You can run select, update, delete, any valid sql statement against the data base.
You can run multiple statements at once, just make sure you have ; at the end of each statement.
How_to_run_database_update_scripts.doc Page 3
Other Tips:
Back up the Database:
If you right click on the database you can select Backup and create a .backup file of the data base.
This is fine for a temporary back up, but make sure you set the data base to be backed up through
Webmin on the server. Make sure you have a folder that is owned by postgres on the server. (we
usually create a db_repository folder under /home.) In webmin, go into the database and at the bottom
select Backup. Select the folder and name to be backed up. Select Custom as the back up type. We
normally select simple back up nightly at midnight. This will back up the data base nightly onto the
server. The database can be copied to off line storage using your network back up facilities.
Periodically you should create on off site backup of the database.
How_to_run_database_update_scripts.doc Page 4
Viewing and altering Data:
You can open a table to see the individual field/column names.
Right click and select View Data and then top 100 rows, all rows or filtered rows (allows you to enter
selection criteria.)
You can now delete rows, change data or add rows.
Just be VERY cautious. What ever you do is NOT alterable. So back things up before you make
changes. Also remember you are going outside the program logic and making changes could totally
mess up the application logic. I would ESPECIALLLY caution you against making changes in the
transaction and financial transaction data this way without careful consideration.
If you have a problem please ask us for help.
Ann Richmond
How_to_run_database_update_scripts.doc Page 5