7,614
edits
Changes
Jump to navigation
Jump to search
[[Category: McNair Admin]]==PostgreSQL on edegan.com==
==PostgreSQL at the McNair Center== We have a dedicated Postgres server available for use by McNair interns, affiliates, and researchers. It is available by SSH from inside the Rice network, including directly and through the RDP.
no edit summary
===Connecting to the dbase server===
To connect through the RDP get a copy of PuTTY (put PuTTY.exe on your desktop) from:
E:/McNair/Installsinstalls
Connect via SSH directly to: reseacher@128199.42188.44177.181215
or
researcher@dbasessh.mcnaircenteredegan.orgcom Or from the RDP you can stay inside the private network and connect to: researcher@192.168.2.92
All of the data files (tab-delimited text) that need to be loaded in and out of a dbase for your project should be stored in:
Z:/Bulkbulk/YourDbase
Note: To make your life easy, map the database's bulk drive on your RDP account. [[Help:Access_RDP_Sever#Connecting_the_Database_ServerMapping the Database Server as Z|Follow the instructions]] to do this. We refer to the database server's bulk drive as either 181dbase/bulk (where 181 is the last segment of its address) or as Z:, as this is the drive letter most commonly mapped to.
==Working with psql==
Note: to use a local copy of psql (if you have it installed locally), connect using the username researcher and DBname:
psql -h 128dbase.42edegan.44.181 com -U researcher dbname
You MUST store all of your SQL commands in a file named yourfilename.sql that is stored in:
E:/McNair/Projectsprojects/YourProject/
There are NO EXCEPTIONS to this. All of your code must go into a .sql file. Even exploratory code. You can copy out of there line by line to run code.
===ALTER===
'''DON'T DO THIS. CREATE A NEW TABLE INSTEAD!'''
Change a table with ALTER:
To get into database via terminal:
1) ssh researcher@128ssh.42edegan.44.181com
2) cd \folder_name
3) psql database_name
===Basic Performance Tuning===
Note that the dbase server at ssh.edegan.com does not use the settings below. Its configuration is much more aggressive.
You will almost surely want to 'performance tune' your postgresql database, as the default settings are near useless. In particular edit postgresql.conf (which is in the data directory of your install) to change:
Create a user using pgAdmin or the createuser command:
createuser ed_eganusername
And then create a database again using pgAdmin or the createdb command:
createdb -O ed_egan username DBName
[[admin_classification::IT Build| ]]
