7,649
edits
Changes
Jump to navigation
Jump to search
==Configuring a copy of Postgres on Windows==
Add PostgreSQL to the path if it isn't already:
Control Panel->System->Advanced->Environmental Variables
Add: C:\Program Files\PostgreSQL\9.0\bin
or from a cmd window (though sometimes this doesn't stick): SET PATH=%PATH%;C:\Program Files\=PostgreSQL\9.0\bin;at the McNair Center==
To We have a dedicated Postgres server available for use PLPerl on windowsby McNair interns, affiliates, you will need to be careful to mix and match researchers. It is available by SSH from inside the Rice network, including through the right versions of PostgreSQL and PerlRDP.
Perl version 5.10.1 (a 64bit build is available from [http://www.activestate.com/activeperl/downloads ActiveState]) works with PostgreSQL 9.0.1 (64bit build 1500). To see your PostgreSQL version type:===Connecting to the dbase server===
psql -c "select versionTo connect through the RDP get a copy of PuTTY (put PuTTY.exe on your desktop);" template1from: E:/McNair/Installs
To see your perl version typeConnect via SSH to: reseacher@128.42.44.181 or researcher@dbase.mcnaircenter.org
With these two versions together you should be able Note: To make your life easy, map the database's bulk drive on your RDP account. [[Help:Access_RDP_Sever#Connecting_the_Database_Server|Follow the instructions]] to add plperl do this. We refer to template1 the database server's bulk drive as either 181/bulk (which all new dbs will inheritwhere 181 is the last segment of its address) with or as Z:, as this is the command:drive letter most commonly mapped to.
createlang plperl template1==Working with psql==
There is a [httpAfter you have ssh'd onto the server change directory to your data directory: cd /bulk/www.postgresql.org/docs/7.3/static/reference-client.html list of commands/client applications], with links to documentation, which is useful.yourdir
You will almost surely want to 'performance tune' your postgresql Then connect a database, as the default settings are near useless. In particular edit postgresql.conf (which is in the data directory of your install) to change: psql DBName
shared_buffers = 512MB #Use about 10-15% of available RAM, Note: to use a max of 512Mb on windows effective_cache_size = 3GB #Use half to 3/4 local copy of available RAMpsql (if you have it installed locally), depending on your pref work_mem = 128MB # This is connect using the memory allocated to each query sortusername researcher and DBname: maintenance_work_mem = 256MB #This is for vacuum, and a max of 256 is recommendedpsql -h 128.42.44.181 -U researcher dbname
Note You MUST store all of your SQL commands in a file named yourfilename.sql that with ''work_mem'' this is the allocation to each sort. Each query you run may do many sorts and you may have many users, so this can explode quickly. 128Mb is an aggresive setting that assumes only a single user. See the [httpstored in://wiki.postgresql.org/wiki/Performance_Optimization various documentation resources], especially the [http E:/McNair/wiki.postgresql.orgProjects/wikiYourProject/Tuning_Your_PostgreSQL_Server official performance optimization page] for more information.
Restart the server (on windows use the 'services' control panel) for many changes There are NO EXCEPTIONS to take effect: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.
pg_ctl restart===Useful PostgreSQL commands===
Create a user using pgAdmin or the createuser commandUseful commands are: createuser ed_egan And then create a database again using pgAdmin or the createdb command:\q Quits createdb -O ed_egan DBName ==Postgres.Haas== We have a new dedicated Postgres server \l List all dbases available for use by PhD Students and Faculty only. Access to the server is available from within Haas or over the VPN. To get an account see Ed Egan (in F533). Ed can either set you up with SSH access so that you can create an account on the database server and as many databases as you like, or he can create an account on the database server, and a blank database owner solely by your account, for you.username Once you have a username and password you and a blank database, can use ''psql'' on your local computer (available from [http://psql \i basics.sourceforge.net/]) to connect to the database server as detailed below. You will probably find that this gives you everything you need - you can load data, perform queries, and produce output datasets for analysis (for example in STATA)sql Run script basics. sql \dt List tablesWithout an SSH account you may not be able to use \d tablename Shows the backup and restore command-line tools. We are currently experimenting with ways to overcome this if necessary. Please ask for more information. If you are comfortable using SSH then all advanced functionality will be available, including schema of the ability to mount your R drive using the commands:table mkdir /mnt/username \r Reset the query buffer mount Ctrl-t cifs //bear/username/ /mnt/ed -o user=username c Abort the current query q Go back to the prompt when viewing a datasetCurrently we are running Postgres version 8.1 on this server. An upgrade is being considered, but the core functionality is more than adequate for our purposes. One useful function that is missing is '''unnest' \COPY Psql'', which was introduced in s version 8.4. However, this can be created manually with [http://wiki.postgresql.org/wiki/Array_Unnest details here]. of copy (See below)
Therefore to connect to Postgres.Haas you will type: psql -h postgres.haas.berkeley.edu DBName The 'new' postgres server is currently on 128.32.252.201. Connect using your username and DBname (firstname_data by default): psql -h 128.32.252.201 -U username dbname Useful commands are: \q Quits \i basics.sql Run script basics.sql \dt List tables ===CREATE, DROP, \COPY Psql's version of copy \r Reset the query buffer Ctrl-c Abort the current query ==SQL Commands==
INSERT INTO tablename VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
COPY tablename FROM '/home/user/weather.txt'; --http://www.postgresql.org/docs/8.4/interactive/sql-copy.html
no edit summary
[[Category: McNair Admin]]
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: perl -vZ:/Bulk/YourDbase
===Dumping and Restoring a Database===
This can be done in pgAdmin in Windows, but the commands (needed on Linux with SSH access) are:
pg_restore -d DBName db.backup
==Working with psqlSQL Commands== To connect your psql client to a db type (for localhost don't specify the host): psql -h host DBName
There is a list of [http://www.postgresql.org/docs/7.3/static/sql-commands.html SQL commands] that may help.
DROP TABLE tablename;
CREATE TABLE tablename (
field1 varchar(100),
field2 int,
field3 date,
field4 real
);
Functions can be written in Perl, Python and other languages. See below for more information.
CREATE FUNCTION getreal (text) RETURNS real AS $$
if ($_[0]=~/^\d{1,}\.\d{0,}$/) { return $_[0]; }
DROP Function correctyear(int,int);
'''Do not do any of the following:''' Populate data with COPY, INSERT and UPDATE:
UPDATE tablename SET kind = 'Dramatic' WHERE kind = 'Drama';
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);
'''Instead, always build stack of tables using:'''
CREATE TABLE tablename AS
SELECT * FROM tablename WHERE ...;
DROP TABLE tablename;
Always load/unload data using the PostgreSQL specific copy function below. Always load tab-delimited data that is UTF-8 encoded, with PC or UNIX line endings, and that has a header row. NEVER DEVIATE FROM THIS.
Load using:
\COPY tablename FROM 'filename.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
Unload (copy to txt file) using:
\COPY tablename TO 'filename.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
===SELECT===
Retrieve results with SELECT:
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'tablename';
===ALTER===
DON'T DO THIS. CREATE A NEW TABLE INSTEAD!
Change a table with ALTER:
ALTER TABLE tablename ADD COLUMN colname real;
ALTER TABLE tablename RENAME COLUMN product_no TO product_number;
===EXPLAIN===
Find out how a query will be executed with EXPLAIN (a Postgre command):
return b
$$ LANGUAGE plpythonu;
==Configuring a copy of Postgres on Windows==
If you'd like to set up a copy of PostgreSQL on your windows laptop or desktop, the following instructions may be helpful.
===Adding PostgresSQL to the PATH===
Add PostgreSQL to the path if it isn't already:
Control Panel->System->Advanced->Environmental Variables
Add: C:\Program Files\PostgreSQL\9.0\bin
or from a cmd window (though sometimes this doesn't stick):
SET PATH=%PATH%;C:\Program Files\PostgreSQL\9.0\bin;
===PostgreSQL and Perl===
To use PLPerl on windows, you will need to be careful to mix and match the right versions of PostgreSQL and Perl.
Perl version 5.10.1 (a 64bit build is available from [http://www.activestate.com/activeperl/downloads ActiveState]) works with PostgreSQL 9.0.1 (64bit build 1500). To see your PostgreSQL version type:
psql -c "select version();" template1
To see your perl version type:
perl -v
With these two versions together you should be able to add plperl to template1 (which all new dbs will inherit) with the command:
createlang plperl template1
There is a [http://www.postgresql.org/docs/7.3/static/reference-client.html list of commands/client applications], with links to documentation, which is useful.
===Basic Performance Tuning===
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:
shared_buffers = 512MB #Use about 10-15% of available RAM, to a max of 512Mb on windows
effective_cache_size = 3GB #Use half to 3/4 of available RAM, depending on your pref
work_mem = 128MB # This is the memory allocated to each query sort
maintenance_work_mem = 256MB #This is for vacuum, and a max of 256 is recommended.
Note that with ''work_mem'' this is the allocation to each sort. Each query you run may do many sorts and you may have many users, so this can explode quickly. 128Mb is an aggresive setting that assumes only a single user. See the [http://wiki.postgresql.org/wiki/Performance_Optimization various documentation resources], especially the [http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server official performance optimization page] for more information.
Restart the server (on windows use the 'services' control panel) for many changes to take effect:
pg_ctl restart
===Creating Users and Dbases===
Create a user using pgAdmin or the createuser command:
createuser ed_egan
And then create a database again using pgAdmin or the createdb command:
createdb -O ed_egan DBName
[[admin_classification::IT Build| ]]
