Exporting from Oracle in UTF8

Use NLS_LANG=ENGLISH_UNITED KINGDOM.UTF8

Identifying failed characters in a postgresql migration from Oracle.

Due to a failure somewhere along the line of a migration from another database to postgresql some of the characters have failed. Since there's a large number of tables and we don't know which columns have characters that have failed here's how we did it;

A pg_dump of the database: pg_dump --data-only --column-inserts -U <user> <db> > dump.txt. Failed characters would show up as '?' so I planned to remove any lines from the text file with a bit of reg exp in notepad++: ^((?!\?).)*$ but the dump file being almost 5Gb was too big for notepad++ to handle. So in steps Powershell and we can do a grep style command: select-string .\dump.txt -pattern "\?" > failed_characters.txt

Bear in mind that this will include valid ? characters but it's start.

And then split the resultant file because it's too big to load into Notepad. [Taken from here]

$linecount=0;$i=0;Get-Content .\BIG_LOG_FILE.txt | %{ Add-Content OUT$i.log "$_";$linecount++; if($linecount -eq 3000){$I++;$linecount=0}}

create new points table with intersecting line id

CREATE TABLE points2 AS
(SELECT point.*, line.lineid as line
FROM point INNER JOIN line
ON ST_Intersects(point.wkb_geometry, line.wkb_geometry));

Allowing json/geojson in IIS7

Taken from Muhammad Bilal Awan's post below but pasted here for my own benefit;


Open IIS Manager
Display properties for the IIS Server
Click MIME Types and then add the JSON extension:
File name extension: .json
MIME type: application/json
Go back to the properties for IIS Server
Click on Handler Mappings
Add a script map
Request path: *.json
Executable: C:\WINDOWS\system32\inetsrv\asp.dll
Name: JSON


And the same applies for geojson of course.

http://www.uipress.com/add-json-handler-support-in-iis-7/#.UPax0mKalYh

postgresql replication, load balancing and SPOFs

here's article for setting up two pgpools - so no single point of failure (thanks to Nozomi Anazai)
http://www.pgpool.net/pgpool-web/contrib_docs/watchdog/en.html

here's an article how to set up pgpool to handle replication and load balancing of a postgresql cluster (thanks to Tatsuo Ishii)
http://pgpool.projects.postgresql.org/contrib_docs/simple_sr_setting/index.html

And here's a great presentation from Tom Fry @ OrdnanceSurvey on their setup (spatial databases this time) using pgPool. No replication duties in this case I think as it's only handling select duties. Here.

Show databases and tables in postgresql

show databases:
select datname from pg_database;
show tables:
\dt

postgres ssh-copy-id: ERROR: No identities found

I'm working on hot_standby with postgresql on centos. One thing I got stuck on being a noob at linux was how to create ssh keys for the postgres account. I was following the 3dtin blog on postgresql hot_standby and got stuck at the following point; 
su - postgres 
ssh-copy-id 
ssh-copy-id: ERROR: No identities found 

Ok, remove that pesky selinux. I believe that's at fault. Set SELINUX in /etc/sysconfig/selinux to disabled. 
su - postgres; pwd should be /var/lib/pgsql 
mkdir .ssh 
ssh-keygen (hit defaults) 
ssh-copy-id -i ~./ssh/id_rsa.pub 
check with ssh So if you do that on the pg_master and drop the key on the pg_slave, you should then repeat the other way (as the 3dtin blog says).