https://wiki.postgresql.org/api.php?action=feedcontributions&user=Jkshah&feedformat=atomPostgreSQL wiki - User contributions [en]2024-03-19T10:44:14ZUser contributionsMediaWiki 1.35.13https://wiki.postgresql.org/index.php?title=Postgres_Open_2017&diff=30802Postgres Open 20172017-09-11T05:31:56Z<p>Jkshah: /* Mission */</p>
<hr />
<div>Conference website: [http://2015.postgresopen.org/ PostgresOpen 2017]<br />
<br />
[https://postgresopen.org/events/feedback/pgopen2017/ Conference Feedback]<br />
<br />
Events shown for placeholding purposes. See the "Editing help" for instructions on how to add a link to your slides.<br />
<br />
== Wednesday September 6 (Tutorials) ==<br />
<br />
== Thursday September 7 ==<br />
=== Cyril Magnin ===<br />
* [https://www.hagander.net/talks/PostgreSQL_10.pdf A look at the Elephants trunk - PostgreSQL 10] (Magnus Hagander)<br />
* [http://momjian.us/main/writings/pgsql/window.pdf Postgres Window Magic] (Bruce Momjian)<br />
* Schrödinger's Elephant (Josh Berkus)<br />
* PgBouncer and 20000 TPS at one node: advanced tuning, hacks and problem solving (Victor Yagofarov)<br />
* An ultimate guide to upgrading your PostgreSQL installation (Ilya Kosmodemiansky)<br />
<br />
=== Market Street ===<br />
* [http://anarazel.de/talks/pgopen-sf-2017-09-07/efficiency.pdf Improving postgres' efficiency] (Andres Freund)<br />
* [https://www.joeconway.com/presentations/SecurePostgreSQL-PGOpen2017.pdf Securing PostgreSQL -- Exploring the PostgreSQL STIG and Beyond] (Joe Conway)<br />
* Concurrency Deep-Dive (Tejas Manohar)<br />
* Human Beings Do Not Have a Primary Key (Christophe Pettus)<br />
* PostgreSQL BI Performance & News from XL project (Mark Wong)<br />
<br />
=== Mission ===<br />
* Get to know Google Cloud SQL (Alexis Guajardo, Brett Hesterberg)<br />
* Developing powerful and intelligent apps using the Azure managed PostgreSQL service (Sunil Kamath)<br />
* Monitoring 101 (Jason Yee)<br />
* [http://jkshah.blogspot.com/2017/09/best-practices-with-managed-postgresql.html Best Practices of using Managed PostgreSQL in the Cloud](Jignesh Shah)<br />
* Distributed COUNT(DISTINCT) with HyperLogLog on PostgreSQL (Burak Yucesoy)<br />
<br />
== Friday September 8 ==<br />
<br />
=== Cyril Magnin ===<br />
* [https://www.dropbox.com/s/bst1okvfz61p5et/PgOpen2017%20-%20AuditStar.pptx?dl=0 PastgreSQL: Dead data tells all tales! (Samuel Elston, Srivathsava Rangarajan)]<br />
* [https://dev.panlex.org/wp-content/uploads/2017/09/Postgres-Open-2017-PanLex.pdf PanLex: Using PostgreSQL to implement a massive word-translation graph] (David Kamholz)<br />
* Tuning PostgreSQL for High Write Workloads (Grant McAlister)<br />
* Replication based Multi-Master solutions and design patterns (Tom Kincaid)<br />
* How Postgres Could Index Itself (Andrew Kane)<br />
* Logical Replication.... LIVE! (Robert Treat)<br />
<br />
=== Market Street ===<br />
* The Immortal Postgres Web (Shaun Thomas)<br />
* [https://docs.google.com/presentation/d/1JyLJK-sUWEeMgofGoLttKAYAi1-upO-AEx9ZzM9hBzQ/edit?usp=sharing, Scaling a SaaS application beyond a single Postgres with Citus] (Jesse H. Willett)<br />
* [https://github.com/vatsan/postgresopen-2017 Scalable in-database machine learning with PL/Python] ([https://postgresql.us/events/schedule/pgopen2017/speaker/123-srivatsan-ramanujam/ Srivatsan Ramanujam])<br />
* [https://wiki.postgresql.org/images/0/06/PGOpen_2017_correct_pic.pptx Enhancements to the bitemporal model support: integrity constraints] (Boris Novikov, Chad Slaughter, Henrietta Dombrovskaya)<br />
* Beyond Foreign Data Wrappers: building a data warehouse from heterogeneous external data sources (Chad Slaughter)<br />
* [http://modernization.xformix.com/wp-content/uploads/2017/09/POSTGRESOpen2017.POSTGRESQL_REST_APIs_RAD_FOR_LEGACY_DATA_1.8-.pdf PostgreSQL, REST API’s and Rapid Application Development for Legacy Data] (Charles Finley)<br />
<br />
=== Mission ===<br />
* [https://www.sarahconway.com/slides/postgres-operator.pdf A Kubernetes Operator for PostgreSQL - Architecture and Design] (Sarah Conway)<br />
* ETL Confessions (Corey Huinker)<br />
* [https://sehrope.github.io/postgres-open-2017-application-security/ PostgreSQL Security for Application Developers] (Sehrope Sarkuni)<br />
* [https://migra.djrobstep.com/your-migrations-are-bad-by-djrobstep-postgresopen2017.pdf Your database migrations are bad and you should feel bad] (Robert Lechte)<br />
* PostgreSQL as a distributed computing platform (Marco Slot)<br />
* PostgreSQL at Pandora (Stephen Rees)</div>Jkshahhttps://wiki.postgresql.org/index.php?title=Converting_from_other_Databases_to_PostgreSQL&diff=30368Converting from other Databases to PostgreSQL2017-06-08T21:11:21Z<p>Jkshah: /* Utilities, tools, scripts etc. */</p>
<hr />
<div>== Non-specific ==<br />
<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines to PostgreSQL and back<br />
* [https://github.com/dimitri/pgloader pgloader] knows how to load data from MySQL, SQLite, MS SQL Server, dBase files, CSV files and fixed-width data files, and more. Released under The PostgreSQL Licence.<br />
* [https://dbconvert.com/postgresql/ DBConvert PostgreSQL database migration and sync software] Database conversion and synchronization between PostgreSQL/ Amazon RDS, MySQL, MS SQL Server, SQL Azure, Oracle, MS Access.<br />
* [http://www.easyfrom.net/ Converting data between PostgreSQL and others database formats] ESF Database Migration Toolkit enables you to transfer data across various databases, supporting PostgreSQL, MySQL, Oracle, SQL Server, IBM DB2, Informix, Microsoft Access, Microsoft Excel, dBase, Foxpro, Firbird, SQLite etc. - by Martin Williams<br />
* [http://troels.arvin.dk/db/rdbms/ Comparison of different SQL implementations] by Troels Arvin (covers PG 8.4 and MySQL 5.0)<br />
* [[Transactional DDL in PostgreSQL: A Competitive Analysis]] by Greg Smith<br />
* [[Migrating from one database to another with Pentaho ETL]] by Nicola Benaglia<br />
* [http://www.vive.net/products/datapro.htm dataPro] Conversion tool for PostgreSQL, SQLite, MySQL, Oracle, SQL Server and Microsoft Access. Transfer database objects between different databases and servers, convert tables schema and migrate data from one database type to another.<br />
* [http://www.datanamic.com/datadiff-crossdb/ DataDiff CrossDB] is a Windows GUI utility to compare and synchronize/transfer data from PostgreSQL to/from Oracle, MSSQL, MS Access or MySQL databases.<br />
* [http://www.sqlmaestro.com/products/postgresql/datawizard/ PostgreSQL Data Wizard] is a Windows GUI utility to transfer both schema and data from any ADO-compatible source (like MS Access, MySQL, SQL Server, Oracle, etc) to PostgreSQL.<br />
* [https://metacpan.org/module/SQL::Translator SQL::Translator] is a Perl module for translating table definitions between different software.<br />
* [[Foreign_data_wrappers|Foreign data wrappers]] may be useful for exporting data from other databases<br />
* [http://www.convert-in.com/pgskit.htm Postgres Migration Toolkit] Software pack to convert Oracle, MySQL, SQL Server and FoxPro to PostgreSQL, and vice versa.<br />
* [https://github.com/ei-grad/sqlacrossover sqlacrossover] SQLAlchemy-based cross-database migration tool<br />
* [https://skyvia.com Skyvia] Web service for cloud data integration for PostgreSQL with Salesforce, Dynamics CRM, SugarCRM, Zoho CRM, QuickBooks, FreshBooks, ExactTarget, MailChimp, Bigcommerce, MySQL, SQL Server, SQL Azure, Amazon RDS.<br />
* [https://github.com/OmniDB/OmniDB OmniDB] Open source web tool for database management and conversion. Currently supports PostgreSQL, Oracle, MariaDB, MySQL, Firebird, SQLite, MS Access, MS SQL Server, MS SQL Compact. OmniDB converts from any supported RDBMS to PostgreSQL and back.<br />
<br />
== Apache Derby ==<br />
<br />
* [https://github.com/billrobertson42/derby2pg derby2pg] Converter program that will convert the tables, data and indexes in a given schema to a script that can be used to populate a PostgreSQL database.<br />
<br />
== DBase II, III, IV+ / DBF Format ==<br />
<br />
* [http://www.convert-in.com/dbf2pgs.htm FoxPro-to-PostgreSQL] a program to migrate FoxPro databases to PostgreSQL server. The program does not use ODBC or any other middleware software. Command line support allows to script, automate and schedule the conversion process.<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from DBF (dBase, Clipper, Visual FoxPro and other DBF variants) to PostgreSQL and back<br />
* [https://github.com/dimitri/pgloader pgloader] by Dimitri Fontaine<br />
* [http://www.tv.com.pl/stepbystep/dbasepsql/ Convert .dbf files into PostgreSQL] by Tomasz Judycki<br />
* [[:Image:26.zip|A Visual Basic utility to convert Dbase III, IV, and V]] by Dennis Bazan<br />
* [[Porting data from dBASE IV to PostgreSQL]] by Vijay Deval (2002-09-07)<br />
* [https://github.com/kstrauser/pgdbf PgDBF : Simplified and optimized replacement for XBaseToPg] by Kirk Strauser<br />
<br />
== FileMaker Pro ==<br />
<br />
* [[Porting from FileMaker Pro to PostgreSQL]] by Michelle Murrain (24th August 2001)<br />
<br />
== IBM DB2 ==<br />
<br />
* [[:Image:DB2UDB-to-PG.pdf|Migrating from DB2 to PostgreSQL]]<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including IBM DB2, to PostgreSQL and back<br />
* [https://github.com/dalibo/db2topg/ db2topg] Migration tool to convert a DB2 UDB Database into a PostgreSQL database<br />
<br />
== Interbase ==<br />
<br />
* [https://github.com/kstrauser/dbreplicate DBReplicate - Simplify Interbase->PostgreSQL conversions] by Kirk Strauser<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including Interbase, to PostgreSQL and back<br />
<br />
== Microsoft Access ==<br />
<br />
* [http://www.postgresonline.com/journal/index.php?url=archives/346-Querying-MS-Access-and-other-ODBC-data-sources-with-OGR_FDW.html Linking to MS Access tables with ogr_fdw] If your PostgreSQL is on windows, you can use ogr_fdw foreign data wrapper, packaged with PostGIS 2.2+ bundle for windows via application stackbuilder. With PostgresQL 9.5, you can use IMPORT FOREIGN SCHEMA to link in all the MS access tables and then cherry pick how you want to restructure.<br />
* [http://docman.sourceforge.net/home_html/projects/sql/exportSQL3.txt exportSQL] - a Microsoft Access module which exports Access Database into MySQL, mSQL and PostgreSQL by Dobrica Pavlinusic. Based on the work of Pedro Freire<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including Access, to PostgreSQL and back<br />
* [http://mdbtools.sourceforge.net/ MDB Tools] by Brian Bruns<br />
** A quick way to dump all tables as tsv or csv files<br />
for TT in $(mdb-tables file.mdb); do<br />
mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.tsv"<br />
done<br />
<br />
for TT in $(mdb-tables file.mdb); do<br />
mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"<br />
done<br />
<br />
If the tablenames have embedded spaces...<br />
<br />
mdb-tables -1 file.mdb| while read TT<br />
do<br />
mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"<br />
done<br />
<br />
A shell script that may be useful for converting entire databases:<br />
<br />
#!/bin/sh -e<br />
<br />
mdbfn=$1<br />
schemafn=$2<br />
fkfn=$3<br />
datafn=$4<br />
schema=$5<br />
<br />
tf=$(tempfile)<br />
<br />
pre=""<br />
[ -n "${schema}" ] && pre="\"${schema}\"."<br />
<br />
mdb-schema "${mdbfn}" postgres > "${tf}"<br />
<br />
# Schema file<br />
echo "BEGIN;\n" > "${schemafn}"<br />
<br />
sp=""<br />
[ -n "${schema}" ] && echo "CREATE SCHEMA \"${schema}\";\n" >> "${schemafn}"<br />
[ -n "${schema}" ] && sp="SET search_path = \"${schema}\", pg_catalog;\n" <br />
<br />
echo ${sp} >> "${schemafn}"<br />
<br />
awk '($0 !~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${schemafn}"<br />
<br />
echo "\nEND;" >> "${schemafn}"<br />
<br />
# Foreign keys file<br />
echo "BEGIN;\n" > "${fkfn}"<br />
echo ${sp} >> "${fkfn}"<br />
<br />
awk '($0 ~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${fkfn}"<br />
<br />
echo "\nEND;" >> "${fkfn}"<br />
<br />
# Data file<br />
echo "BEGIN;\n" > "${datafn}"<br />
echo "SET CONSTRAINTS ALL DEFERRED;\n" >> "${datafn}"<br />
<br />
mdb-tables -1 "${mdbfn}" | while read TT<br />
do<br />
mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' "${mdbfn}" "$TT" > "${tf}"<br />
<br />
awk -v pre="${pre}" -v TT="${TT}" \<br />
'(NR==1) {gsub(/\t/,"\",\""); print "COPY " pre "\"" TT "\"(\"" $0 "\") FROM stdin;";}' "${tf}" >> "${datafn}"<br />
awk '(NR>1) {gsub(/\t\t/,"\t\\N\t"); gsub(/\t$/,"\t\\N"); gsub(/\t\t/,"\t\\N\t"); print;}' "${tf}" >> "${datafn}"<br />
<br />
echo "\\.\n" >> "${datafn}"<br />
done<br />
<br />
echo "END;" >> "${datafn}"<br />
<br />
rm -f "${tf}"<br />
<br />
If this script is saved to the file access2psql.sh and made executable, then it would be used as follows:<br />
<br />
access2psql.sh file.mdb schema.sql foreignkeys.sql data.sql pg_schema_name<br />
psql -f schema.sql pg_db_name<br />
psql -f data.sql pg_db_name<br />
psql -f foreignkeys.sql pg_db_name<br />
<br />
This script won't work properly if there are tab characters in text columns, though the call to mdb-export could be modified to export INSERT statements to fix this. Also, mdb-schema has trouble representing multi-column foreign keys, so foreignkeys.sql may need some manual editing.<br />
<br />
* [http://convertdb.com/access/postgresql/ Export Microsoft Access to PostgreSQL. Cross database synchronization]<br />
* [http://www.olschimke.eu/2012/08/07/importing-microsoft-access-mdb-into-postgresql-on-linux-postgres/ Importing Microsoft Access MDB into PostgreSQL on Linux] by Michael Olschimke<br />
* [[Microsoft Access to PostgreSQL Conversion]] by Jon Hutchings (2001-07-20)<br />
* [http://pgfoundry.org/projects/access2pgsql/ access2pgsql tool] by Mariano Reingart<br />
* [http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html Using MS Access as front end and PostgreSQL as back-end database] by PostgresOnLine Journal - 2008-01-31<br />
<br />
== Microsoft SQL Server ==<br />
<br />
* [http://www.convert-in.com/mss2pgs.htm MSSQL-to-PostgreSQL] is a migration utility to convert SQL Server or SQL Azure databases to PostgreSQL. Option to filter data using SELECT-queries, synchronization mode, command line support.<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including SQL Server, to PostgreSQL and back<br />
* [http://dalibo.github.io/sqlserver2pgsql/ sqlserver2pgsql] Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database<br />
* [https://github.com/dimitri/pgloader pgloader] by Dimitri Fontaine<br />
* [https://dbconvert.com/mssql/postgresql/ DBConvert for PostgreSQL and MS SQL Server] Conversion and Sync software solutions for cross database migration between PostgreSQL/ Amazon RDS and MS SQL Server/ SQL Azure<br />
* [http://sqlblog.com/blogs/alexander_kuznetsov/archive/tags/PostgreSql/default.aspx Learning PostgreSQL (Alexander Kuznetsov)] - series of blog articles for SQL Server users (2013-10 ~ 2013-11)<br />
* [http://www.postgresonline.com/journal/archives/219-SQL-Server-to-PostgreSQL-Converting-table-structure.html Converting SQL Server Table Structure to PostgreSQL] Leo Hsu and Regina Obe (2011-09-03)<br />
* [[:Image:5.pdf|Conversion of Microsoft SQL/ASP applications to PostgreSQL]] by Ethan Townsend (2005-06-23)<br />
* [[Converting your data from MS SQL Server 7 to PostgreSQL 7.1.x]] by Ryan C. Bonham (2002-01-05)<br />
* [[Microsoft SQL Server to PostgreSQL Migration by Ian Harding]] (2001-09-17)<br />
* [http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features] BostonGIS (2010-06-01) <br />
* [http://www.postgresonline.com/journal/index.php?/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html Cross Compare of SQL Server 2005, MySQL 5, and PostgreSQL 8.3] by Leo Hsu and Regina Obe (2008-05-13)<br />
* [http://www.postgresonline.com/journal/index.php?/archives/130-Cross-Compare-of-PostgreSQL-8.4,-SQL-Server-2008,-MySQL-5.1.html Cross Compare of SQL Server 2008, MySQL 5.1, and PostgreSQL 8.4] by Leo Hsu and Regina Obe (2009-08-15)<br />
* [http://www.postgresonline.com/journal/archives/179-Universal-Unique-Identifiers-PostgreSQL-SQL-Server-Compare.html Universal Unique Identifiers PostgreSQL SQL Server Compare] Demonstrates how to implement SQL Server NEWID() GUID uniqueidentifier type in PostgreSQL by Leo Hsu and Regina Obe (2010-10-07)<br />
<br />
== MySQL ==<br />
<br />
<br />
=== Scripts, programs ===<br />
<br />
[https://github.com/dimitri/pgloader pgloader] supports full migration of the database schema and data from a single command line and provides a command language for more complex or specific scenarios. It is still fully supported: please report any bugs on its GitHub page. It appears in the 2013 section here because that's when it has been published first.<br />
<br />
<br />
==== 2015 ====<br />
* [http://www.convert-in.com/sql2pgs.htm MySQL-to-PostgreSQL] is a program to migrate MySQL databases to PostgreSQL server. Option to filter data using SELECT-queries, synchronization mode, command line support.<br />
* [https://github.com/AnatolyUss/FromMySqlToPostgreSql FromMySqlToPostgreSql migration tool by Anatoly Khaytovich], provides an accurate migration of table data, indices, PKs, FKs... Makes an extensive use of PostgreSQL COPY protocol.<br />
<br />
==== 2014 ====<br />
<br />
* [https://github.com/mihailShumilov/mysql2postgresql MySQL/PostgreSQL Converter from Mihail Shumilov], written on PHP. Supported big DB<br />
<br />
==== 2013 ====<br />
* [https://github.com/lanyrd/mysql-postgresql-converter MySQL/PostgreSQL Converter from Lanyrd], based on [http://lanyrd.com/blog/2012/lanyrds-big-move/ work detailed here]<br />
* [https://github.com/dimitri/pgloader pgloader], from Dimitri Fontaine, based on [http://tapoueh.org/blog/2013/01/28-pgloader-future work detailed here] and [http://tapoueh.org/blog/2013/11/12-migrating-sakila Migrating Sakila from MySQL to PostgreSQL]<br />
* [https://github.com/philipsoutham/py-mysql2pgsql py-mysql2pgsql], [https://github.com/mozilla/airmozilla/pull/61/files#diff-30 example use case here]<br />
* [https://github.com/bdigital/my_etl MyETL], Tool that works with the [https://github.com/bdigital/mysql_fdw MySQL FDW]<br />
<br />
==== Previously ====<br />
* [http://www.enterprisedb.com/products/download.do Free MySQL to Postgres Migration Wizard by EnterpriseDB v1.1] by EnterpriseDB Corporation. Available without registration via PostgreSQL installer wizard.<br />
* [http://www.pgsql.com/download/ Conversion tool for migrating from MySQL to PostgreSQL] by PostgreSQL Inc.<br />
* [http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL Converting MySQL to PostgreSQL] - from en.wikibooks.org<br />
* [http://convertdb.com/mysql/postgresql/ Migrate MySQL to PostgreSQL.] - Cross database bidirectional synchronization<br />
* [http://www.sourcefiles.org/Databases/Utilities/Conversion/my2pg.pl my2pg.pl] - A Perl script used to convert a MySQL database dump to PostgreSQL-compatible format, by Maxim Rudensky and Valentine Danilchuk<br />
* [http://pgfoundry.org/projects/mysql2pgsql/ mysql2pgsql] - A Perl script used to convert MySQL databases dump to a PostgreSQL-compatible format<br />
* [http://mp2p.mikekohn.net/ MySQL PHP to PostgreSQL] by Michael Kohn<br />
* [http://www.gab.lc/script_php_my2pg.php PHP_my2pg] PHP script by Gabriel Bordeaux<br />
* [http://tryolabs.com/Blog/2012/02/10/django-migrating-mysql-postgresql/ Migrating from MySQL to Postgresql with Django] blogpost by alejandro<br />
* [http://github.com/maxlapshin/mysql2postgres Migrates MySQL to PostgreSQL] Ruby script by Max Lapshin<br />
# To install mysql2psql (under ubuntu 11.10): No need to get from github, just:<br />
sudo apt-get install ruby gems libmysqlclient-dev libpq-dev<br />
gem install mysql pg mysql2psql<br />
# To get info about the mysql socket:<br />
netstat -l | grep mysql<br />
mysql2psql # creates a .yml templae<br />
vi mysql2psql.yml # edit the template<br />
mysql2psql # connects to mysql database and write into postgres database<br />
<br />
* [http://pypi.python.org/pypi/py-mysql2pgsql py-mysql2pgsql] Python script to convert MySQL database to PostgreSQL compatible file or export directly to a PostgreSQL server<br />
* [https://bitbucket.org/iceone/mysql2pgsql/ my2pg.py] MySQL to PostgreSQL database conversion (not the same as above)<br />
* [http://phpseclib.sourceforge.net/sql/ SQL Data Definition Language (DDL) Conversion to MySQL, PostgreSQL and MS-SQL] by phpBB Group<br />
<br />
=== Documentation ===<br />
<br />
* [http://www.sitepoint.com/article/site-mysql-postgresql-1 Backend Database Switcheroo Howto Part I] by Nathan Matias<br />
* [http://www.sitepoint.com/article/site-mysql-postgresql-2 Backend Database Switcheroo Howto Part II] by Nathan Matias<br />
* [http://www.xach.com/aolserver/mysql-to-postgresql.html How-To: Migrating from MySQL to PostgreSQL] by Zach Beane<br />
* [[Things to find out about when moving from MySQL to PostgreSQL]] by Joel Burton (8th April 2001)<br />
* [[Why PostgreSQL Instead of MySQL: Comparing Reliability and Speed in 2007]] by Greg Smith<br />
* [http://www.postgresonline.com/journal/index.php?/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html Cross Compare of SQL Server, MySQL, and PostgreSQL] by Leo Hsu and Regina Obe (2008-05-13)<br />
* [[How to make a proper migration from MySQL to PostgreSQL]]<br />
* [http://andreas.scherbaum.la/writings/migrate_web_platforms.pdf Migrating a community platform from Mysql to PostgreSQL] by Andreas Scherbaum (Prato, 2007)<br />
* [http://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html MySQL functions for PostgreSQL] by Pavel Stěhule<br />
<br />
== Oracle ==<br />
<br />
=== Utilities, tools, scripts etc. ===<br />
<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines to PostgreSQL and back<br />
* [https://dbconvert.com/oracle/postgresql/ Database migration for Oracle and PostgreSQL] Software for cross - database conversion and sync between Oracle and PostgreSQL.<br />
* [http://ora2pg.darold.net/ Ora2Pg - Oracle to PostgreSQL database schema converter] by Gilles Darold<br />
* [http://oracle-fdw.projects.pgfoundry.org/ PostgreSQL Foreign Data Wrapper for Oracle (oracle_fdw)] - an [[Fdw|FDW]] providing support to access Oracle databases from within PostgreSQL<br />
* [http://orafce.projects.pgfoundry.org/ Orafce] - implements common Oracle functions in PostgreSQL for compatibility<br />
* [http://www.convert-in.com/ora2pgs.htm Oracle-to-PostgreSQL] - a program to migrate Oracle databases to PostgreSQL server. The program has high performance due to direct connection to data source and destination databases (it does not use ODBC or any other middleware software). Command line support allows to script, automate and schedule the conversion process.<br />
* [http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_SchemaConversionTool.Installing.html AWS Schema Conversion Tool] - to convert your existing database schema from one database engine to another (free download).<br />
<br />
=== Documentation, articles, presentations etc. ===<br />
<br />
==== PostgreSQL documentation ====<br />
* [http://www.postgresql.org/docs/current/interactive/plpgsql-porting.html Porting from Oracle PL/SQL]<br />
<br />
==== Articles and presentations (in reverse chronological order) ====<br />
<br />
* [http://tapoueh.org/confs/2013/10/29-pgconfeu-2013 From MySQL to PostgreSQL with pgloader] (English) talk at PostgreSQL Conference Europe, Dimitri Fontaine, 2013-10-29<br />
* [http://wiki.cenatic.es/wikiesp/index.php/Procedimiento_de_An%C3%A1lisis_y_Planificaci%C3%B3n_de_Migraci%C3%B3n_de_Oracle_10g_R2_a_PostgreSQL_9.1.3 Analysis and Planning Process for Migration from Oracle 10g R2 to PostgreSQL 9.1.3] (Spanish) by the Spanish National Competence Center for the Application of Opensource Technologies [http://www.cenatic.es (CENATIC)] (2012-06)<br />
* [http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features] BostonGIS (2010-06-01)<br />
* [http://wiki.kandalaya.org/cgi-bin/twiki/view/Main/MigratingOracleToPostgreSQL Migrating Oracle Databases to PostgreSQL using Xen on Debian GNU/Linux] by Raj Mathur (Kandalaya) (2008-12-26)<br />
* [http://www.pgcon.org/2008/schedule/track/Tutorial/62.en.html Porting Oracle Applications to PostgreSQL] by Peter Eisentraut (PGCon 2008)<br />
* [[PostgreSQL for Oracle DBAs]] by Richard Stephan (2007-06-01)<br />
* [[:Image:Pg 8.1 J2EE v1.0.pdf|Porting JDBC applications from Oracle to PostgreSQL]] by Chris Drawater (2006-03-24)<br />
** [http://www.holindis.co.uk/ Related documents for J2EE, Tomcat, and Oracle migrations] by Chris Drawater (2007-01-15)<br />
* [[Interview with Mark Stosberg (From Oracle/tcl to PostgreSQL/Perl)]] by Robert Treat (2004-10-24)<br />
* [[Oracle to Postgres Conversion]] by James Shannon, Ben Adida, and Don Baccus<br />
<br />
== Progress RDBMS ==<br />
<br />
* [http://knol.google.com/k/tomasz-judycki/convert-progress-rdbms-to-postgresql/2rifd79ifooa9/4# Convert Progress RDBMS to PostgreSQL] by Tomasz Judycki<br />
<br />
== Converting PostgreSQL Databases to other Databases ==<br />
<br />
* [http://docman.sourceforge.net/home_html/projects/sql/pgsql2interbase PostgreSQL to InterBase dump file converter] by Dobrica Pavlinusic<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from PostgreSQL to more than 30 database engines<br />
* [https://github.com/OmniDB/OmniDB OmniDB] Open source web tool for database management and conversion. Currently supports PostgreSQL, Oracle, MariaDB, MySQL, Firebird, SQLite, MS Access, MS SQL Server, MS SQL Compact. OmniDB converts from any supported RDBMS to PostgreSQL and back.<br />
<br />
[[Category:Install]]<br />
[[Category:General articles and guides]]</div>Jkshahhttps://wiki.postgresql.org/index.php?title=Converting_from_other_Databases_to_PostgreSQL&diff=30367Converting from other Databases to PostgreSQL2017-06-08T21:00:39Z<p>Jkshah: /* Utilities, tools, scripts etc. */</p>
<hr />
<div>== Non-specific ==<br />
<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines to PostgreSQL and back<br />
* [https://github.com/dimitri/pgloader pgloader] knows how to load data from MySQL, SQLite, MS SQL Server, dBase files, CSV files and fixed-width data files, and more. Released under The PostgreSQL Licence.<br />
* [https://dbconvert.com/postgresql/ DBConvert PostgreSQL database migration and sync software] Database conversion and synchronization between PostgreSQL/ Amazon RDS, MySQL, MS SQL Server, SQL Azure, Oracle, MS Access.<br />
* [http://www.easyfrom.net/ Converting data between PostgreSQL and others database formats] ESF Database Migration Toolkit enables you to transfer data across various databases, supporting PostgreSQL, MySQL, Oracle, SQL Server, IBM DB2, Informix, Microsoft Access, Microsoft Excel, dBase, Foxpro, Firbird, SQLite etc. - by Martin Williams<br />
* [http://troels.arvin.dk/db/rdbms/ Comparison of different SQL implementations] by Troels Arvin (covers PG 8.4 and MySQL 5.0)<br />
* [[Transactional DDL in PostgreSQL: A Competitive Analysis]] by Greg Smith<br />
* [[Migrating from one database to another with Pentaho ETL]] by Nicola Benaglia<br />
* [http://www.vive.net/products/datapro.htm dataPro] Conversion tool for PostgreSQL, SQLite, MySQL, Oracle, SQL Server and Microsoft Access. Transfer database objects between different databases and servers, convert tables schema and migrate data from one database type to another.<br />
* [http://www.datanamic.com/datadiff-crossdb/ DataDiff CrossDB] is a Windows GUI utility to compare and synchronize/transfer data from PostgreSQL to/from Oracle, MSSQL, MS Access or MySQL databases.<br />
* [http://www.sqlmaestro.com/products/postgresql/datawizard/ PostgreSQL Data Wizard] is a Windows GUI utility to transfer both schema and data from any ADO-compatible source (like MS Access, MySQL, SQL Server, Oracle, etc) to PostgreSQL.<br />
* [https://metacpan.org/module/SQL::Translator SQL::Translator] is a Perl module for translating table definitions between different software.<br />
* [[Foreign_data_wrappers|Foreign data wrappers]] may be useful for exporting data from other databases<br />
* [http://www.convert-in.com/pgskit.htm Postgres Migration Toolkit] Software pack to convert Oracle, MySQL, SQL Server and FoxPro to PostgreSQL, and vice versa.<br />
* [https://github.com/ei-grad/sqlacrossover sqlacrossover] SQLAlchemy-based cross-database migration tool<br />
* [https://skyvia.com Skyvia] Web service for cloud data integration for PostgreSQL with Salesforce, Dynamics CRM, SugarCRM, Zoho CRM, QuickBooks, FreshBooks, ExactTarget, MailChimp, Bigcommerce, MySQL, SQL Server, SQL Azure, Amazon RDS.<br />
* [https://github.com/OmniDB/OmniDB OmniDB] Open source web tool for database management and conversion. Currently supports PostgreSQL, Oracle, MariaDB, MySQL, Firebird, SQLite, MS Access, MS SQL Server, MS SQL Compact. OmniDB converts from any supported RDBMS to PostgreSQL and back.<br />
<br />
== Apache Derby ==<br />
<br />
* [https://github.com/billrobertson42/derby2pg derby2pg] Converter program that will convert the tables, data and indexes in a given schema to a script that can be used to populate a PostgreSQL database.<br />
<br />
== DBase II, III, IV+ / DBF Format ==<br />
<br />
* [http://www.convert-in.com/dbf2pgs.htm FoxPro-to-PostgreSQL] a program to migrate FoxPro databases to PostgreSQL server. The program does not use ODBC or any other middleware software. Command line support allows to script, automate and schedule the conversion process.<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from DBF (dBase, Clipper, Visual FoxPro and other DBF variants) to PostgreSQL and back<br />
* [https://github.com/dimitri/pgloader pgloader] by Dimitri Fontaine<br />
* [http://www.tv.com.pl/stepbystep/dbasepsql/ Convert .dbf files into PostgreSQL] by Tomasz Judycki<br />
* [[:Image:26.zip|A Visual Basic utility to convert Dbase III, IV, and V]] by Dennis Bazan<br />
* [[Porting data from dBASE IV to PostgreSQL]] by Vijay Deval (2002-09-07)<br />
* [https://github.com/kstrauser/pgdbf PgDBF : Simplified and optimized replacement for XBaseToPg] by Kirk Strauser<br />
<br />
== FileMaker Pro ==<br />
<br />
* [[Porting from FileMaker Pro to PostgreSQL]] by Michelle Murrain (24th August 2001)<br />
<br />
== IBM DB2 ==<br />
<br />
* [[:Image:DB2UDB-to-PG.pdf|Migrating from DB2 to PostgreSQL]]<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including IBM DB2, to PostgreSQL and back<br />
* [https://github.com/dalibo/db2topg/ db2topg] Migration tool to convert a DB2 UDB Database into a PostgreSQL database<br />
<br />
== Interbase ==<br />
<br />
* [https://github.com/kstrauser/dbreplicate DBReplicate - Simplify Interbase->PostgreSQL conversions] by Kirk Strauser<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including Interbase, to PostgreSQL and back<br />
<br />
== Microsoft Access ==<br />
<br />
* [http://www.postgresonline.com/journal/index.php?url=archives/346-Querying-MS-Access-and-other-ODBC-data-sources-with-OGR_FDW.html Linking to MS Access tables with ogr_fdw] If your PostgreSQL is on windows, you can use ogr_fdw foreign data wrapper, packaged with PostGIS 2.2+ bundle for windows via application stackbuilder. With PostgresQL 9.5, you can use IMPORT FOREIGN SCHEMA to link in all the MS access tables and then cherry pick how you want to restructure.<br />
* [http://docman.sourceforge.net/home_html/projects/sql/exportSQL3.txt exportSQL] - a Microsoft Access module which exports Access Database into MySQL, mSQL and PostgreSQL by Dobrica Pavlinusic. Based on the work of Pedro Freire<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including Access, to PostgreSQL and back<br />
* [http://mdbtools.sourceforge.net/ MDB Tools] by Brian Bruns<br />
** A quick way to dump all tables as tsv or csv files<br />
for TT in $(mdb-tables file.mdb); do<br />
mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.tsv"<br />
done<br />
<br />
for TT in $(mdb-tables file.mdb); do<br />
mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"<br />
done<br />
<br />
If the tablenames have embedded spaces...<br />
<br />
mdb-tables -1 file.mdb| while read TT<br />
do<br />
mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"<br />
done<br />
<br />
A shell script that may be useful for converting entire databases:<br />
<br />
#!/bin/sh -e<br />
<br />
mdbfn=$1<br />
schemafn=$2<br />
fkfn=$3<br />
datafn=$4<br />
schema=$5<br />
<br />
tf=$(tempfile)<br />
<br />
pre=""<br />
[ -n "${schema}" ] && pre="\"${schema}\"."<br />
<br />
mdb-schema "${mdbfn}" postgres > "${tf}"<br />
<br />
# Schema file<br />
echo "BEGIN;\n" > "${schemafn}"<br />
<br />
sp=""<br />
[ -n "${schema}" ] && echo "CREATE SCHEMA \"${schema}\";\n" >> "${schemafn}"<br />
[ -n "${schema}" ] && sp="SET search_path = \"${schema}\", pg_catalog;\n" <br />
<br />
echo ${sp} >> "${schemafn}"<br />
<br />
awk '($0 !~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${schemafn}"<br />
<br />
echo "\nEND;" >> "${schemafn}"<br />
<br />
# Foreign keys file<br />
echo "BEGIN;\n" > "${fkfn}"<br />
echo ${sp} >> "${fkfn}"<br />
<br />
awk '($0 ~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${fkfn}"<br />
<br />
echo "\nEND;" >> "${fkfn}"<br />
<br />
# Data file<br />
echo "BEGIN;\n" > "${datafn}"<br />
echo "SET CONSTRAINTS ALL DEFERRED;\n" >> "${datafn}"<br />
<br />
mdb-tables -1 "${mdbfn}" | while read TT<br />
do<br />
mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' "${mdbfn}" "$TT" > "${tf}"<br />
<br />
awk -v pre="${pre}" -v TT="${TT}" \<br />
'(NR==1) {gsub(/\t/,"\",\""); print "COPY " pre "\"" TT "\"(\"" $0 "\") FROM stdin;";}' "${tf}" >> "${datafn}"<br />
awk '(NR>1) {gsub(/\t\t/,"\t\\N\t"); gsub(/\t$/,"\t\\N"); gsub(/\t\t/,"\t\\N\t"); print;}' "${tf}" >> "${datafn}"<br />
<br />
echo "\\.\n" >> "${datafn}"<br />
done<br />
<br />
echo "END;" >> "${datafn}"<br />
<br />
rm -f "${tf}"<br />
<br />
If this script is saved to the file access2psql.sh and made executable, then it would be used as follows:<br />
<br />
access2psql.sh file.mdb schema.sql foreignkeys.sql data.sql pg_schema_name<br />
psql -f schema.sql pg_db_name<br />
psql -f data.sql pg_db_name<br />
psql -f foreignkeys.sql pg_db_name<br />
<br />
This script won't work properly if there are tab characters in text columns, though the call to mdb-export could be modified to export INSERT statements to fix this. Also, mdb-schema has trouble representing multi-column foreign keys, so foreignkeys.sql may need some manual editing.<br />
<br />
* [http://convertdb.com/access/postgresql/ Export Microsoft Access to PostgreSQL. Cross database synchronization]<br />
* [http://www.olschimke.eu/2012/08/07/importing-microsoft-access-mdb-into-postgresql-on-linux-postgres/ Importing Microsoft Access MDB into PostgreSQL on Linux] by Michael Olschimke<br />
* [[Microsoft Access to PostgreSQL Conversion]] by Jon Hutchings (2001-07-20)<br />
* [http://pgfoundry.org/projects/access2pgsql/ access2pgsql tool] by Mariano Reingart<br />
* [http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html Using MS Access as front end and PostgreSQL as back-end database] by PostgresOnLine Journal - 2008-01-31<br />
<br />
== Microsoft SQL Server ==<br />
<br />
* [http://www.convert-in.com/mss2pgs.htm MSSQL-to-PostgreSQL] is a migration utility to convert SQL Server or SQL Azure databases to PostgreSQL. Option to filter data using SELECT-queries, synchronization mode, command line support.<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including SQL Server, to PostgreSQL and back<br />
* [http://dalibo.github.io/sqlserver2pgsql/ sqlserver2pgsql] Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database<br />
* [https://github.com/dimitri/pgloader pgloader] by Dimitri Fontaine<br />
* [https://dbconvert.com/mssql/postgresql/ DBConvert for PostgreSQL and MS SQL Server] Conversion and Sync software solutions for cross database migration between PostgreSQL/ Amazon RDS and MS SQL Server/ SQL Azure<br />
* [http://sqlblog.com/blogs/alexander_kuznetsov/archive/tags/PostgreSql/default.aspx Learning PostgreSQL (Alexander Kuznetsov)] - series of blog articles for SQL Server users (2013-10 ~ 2013-11)<br />
* [http://www.postgresonline.com/journal/archives/219-SQL-Server-to-PostgreSQL-Converting-table-structure.html Converting SQL Server Table Structure to PostgreSQL] Leo Hsu and Regina Obe (2011-09-03)<br />
* [[:Image:5.pdf|Conversion of Microsoft SQL/ASP applications to PostgreSQL]] by Ethan Townsend (2005-06-23)<br />
* [[Converting your data from MS SQL Server 7 to PostgreSQL 7.1.x]] by Ryan C. Bonham (2002-01-05)<br />
* [[Microsoft SQL Server to PostgreSQL Migration by Ian Harding]] (2001-09-17)<br />
* [http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features] BostonGIS (2010-06-01) <br />
* [http://www.postgresonline.com/journal/index.php?/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html Cross Compare of SQL Server 2005, MySQL 5, and PostgreSQL 8.3] by Leo Hsu and Regina Obe (2008-05-13)<br />
* [http://www.postgresonline.com/journal/index.php?/archives/130-Cross-Compare-of-PostgreSQL-8.4,-SQL-Server-2008,-MySQL-5.1.html Cross Compare of SQL Server 2008, MySQL 5.1, and PostgreSQL 8.4] by Leo Hsu and Regina Obe (2009-08-15)<br />
* [http://www.postgresonline.com/journal/archives/179-Universal-Unique-Identifiers-PostgreSQL-SQL-Server-Compare.html Universal Unique Identifiers PostgreSQL SQL Server Compare] Demonstrates how to implement SQL Server NEWID() GUID uniqueidentifier type in PostgreSQL by Leo Hsu and Regina Obe (2010-10-07)<br />
<br />
== MySQL ==<br />
<br />
<br />
=== Scripts, programs ===<br />
<br />
[https://github.com/dimitri/pgloader pgloader] supports full migration of the database schema and data from a single command line and provides a command language for more complex or specific scenarios. It is still fully supported: please report any bugs on its GitHub page. It appears in the 2013 section here because that's when it has been published first.<br />
<br />
<br />
==== 2015 ====<br />
* [http://www.convert-in.com/sql2pgs.htm MySQL-to-PostgreSQL] is a program to migrate MySQL databases to PostgreSQL server. Option to filter data using SELECT-queries, synchronization mode, command line support.<br />
* [https://github.com/AnatolyUss/FromMySqlToPostgreSql FromMySqlToPostgreSql migration tool by Anatoly Khaytovich], provides an accurate migration of table data, indices, PKs, FKs... Makes an extensive use of PostgreSQL COPY protocol.<br />
<br />
==== 2014 ====<br />
<br />
* [https://github.com/mihailShumilov/mysql2postgresql MySQL/PostgreSQL Converter from Mihail Shumilov], written on PHP. Supported big DB<br />
<br />
==== 2013 ====<br />
* [https://github.com/lanyrd/mysql-postgresql-converter MySQL/PostgreSQL Converter from Lanyrd], based on [http://lanyrd.com/blog/2012/lanyrds-big-move/ work detailed here]<br />
* [https://github.com/dimitri/pgloader pgloader], from Dimitri Fontaine, based on [http://tapoueh.org/blog/2013/01/28-pgloader-future work detailed here] and [http://tapoueh.org/blog/2013/11/12-migrating-sakila Migrating Sakila from MySQL to PostgreSQL]<br />
* [https://github.com/philipsoutham/py-mysql2pgsql py-mysql2pgsql], [https://github.com/mozilla/airmozilla/pull/61/files#diff-30 example use case here]<br />
* [https://github.com/bdigital/my_etl MyETL], Tool that works with the [https://github.com/bdigital/mysql_fdw MySQL FDW]<br />
<br />
==== Previously ====<br />
* [http://www.enterprisedb.com/products/download.do Free MySQL to Postgres Migration Wizard by EnterpriseDB v1.1] by EnterpriseDB Corporation. Available without registration via PostgreSQL installer wizard.<br />
* [http://www.pgsql.com/download/ Conversion tool for migrating from MySQL to PostgreSQL] by PostgreSQL Inc.<br />
* [http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL Converting MySQL to PostgreSQL] - from en.wikibooks.org<br />
* [http://convertdb.com/mysql/postgresql/ Migrate MySQL to PostgreSQL.] - Cross database bidirectional synchronization<br />
* [http://www.sourcefiles.org/Databases/Utilities/Conversion/my2pg.pl my2pg.pl] - A Perl script used to convert a MySQL database dump to PostgreSQL-compatible format, by Maxim Rudensky and Valentine Danilchuk<br />
* [http://pgfoundry.org/projects/mysql2pgsql/ mysql2pgsql] - A Perl script used to convert MySQL databases dump to a PostgreSQL-compatible format<br />
* [http://mp2p.mikekohn.net/ MySQL PHP to PostgreSQL] by Michael Kohn<br />
* [http://www.gab.lc/script_php_my2pg.php PHP_my2pg] PHP script by Gabriel Bordeaux<br />
* [http://tryolabs.com/Blog/2012/02/10/django-migrating-mysql-postgresql/ Migrating from MySQL to Postgresql with Django] blogpost by alejandro<br />
* [http://github.com/maxlapshin/mysql2postgres Migrates MySQL to PostgreSQL] Ruby script by Max Lapshin<br />
# To install mysql2psql (under ubuntu 11.10): No need to get from github, just:<br />
sudo apt-get install ruby gems libmysqlclient-dev libpq-dev<br />
gem install mysql pg mysql2psql<br />
# To get info about the mysql socket:<br />
netstat -l | grep mysql<br />
mysql2psql # creates a .yml templae<br />
vi mysql2psql.yml # edit the template<br />
mysql2psql # connects to mysql database and write into postgres database<br />
<br />
* [http://pypi.python.org/pypi/py-mysql2pgsql py-mysql2pgsql] Python script to convert MySQL database to PostgreSQL compatible file or export directly to a PostgreSQL server<br />
* [https://bitbucket.org/iceone/mysql2pgsql/ my2pg.py] MySQL to PostgreSQL database conversion (not the same as above)<br />
* [http://phpseclib.sourceforge.net/sql/ SQL Data Definition Language (DDL) Conversion to MySQL, PostgreSQL and MS-SQL] by phpBB Group<br />
<br />
=== Documentation ===<br />
<br />
* [http://www.sitepoint.com/article/site-mysql-postgresql-1 Backend Database Switcheroo Howto Part I] by Nathan Matias<br />
* [http://www.sitepoint.com/article/site-mysql-postgresql-2 Backend Database Switcheroo Howto Part II] by Nathan Matias<br />
* [http://www.xach.com/aolserver/mysql-to-postgresql.html How-To: Migrating from MySQL to PostgreSQL] by Zach Beane<br />
* [[Things to find out about when moving from MySQL to PostgreSQL]] by Joel Burton (8th April 2001)<br />
* [[Why PostgreSQL Instead of MySQL: Comparing Reliability and Speed in 2007]] by Greg Smith<br />
* [http://www.postgresonline.com/journal/index.php?/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html Cross Compare of SQL Server, MySQL, and PostgreSQL] by Leo Hsu and Regina Obe (2008-05-13)<br />
* [[How to make a proper migration from MySQL to PostgreSQL]]<br />
* [http://andreas.scherbaum.la/writings/migrate_web_platforms.pdf Migrating a community platform from Mysql to PostgreSQL] by Andreas Scherbaum (Prato, 2007)<br />
* [http://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html MySQL functions for PostgreSQL] by Pavel Stěhule<br />
<br />
== Oracle ==<br />
<br />
=== Utilities, tools, scripts etc. ===<br />
<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines to PostgreSQL and back<br />
* [https://dbconvert.com/oracle/postgresql/ Database migration for Oracle and PostgreSQL] Software for cross - database conversion and sync between Oracle and PostgreSQL.<br />
* [http://ora2pg.darold.net/ Ora2Pg - Oracle to PostgreSQL database schema converter] by Gilles Darold<br />
* [http://oracle-fdw.projects.pgfoundry.org/ PostgreSQL Foreign Data Wrapper for Oracle (oracle_fdw)] - an [[Fdw|FDW]] providing support to access Oracle databases from within PostgreSQL<br />
* [http://orafce.projects.pgfoundry.org/ Orafce] - implements common Oracle functions in PostgreSQL for compatibility<br />
* [http://www.convert-in.com/ora2pgs.htm Oracle-to-PostgreSQL] - a program to migrate Oracle databases to PostgreSQL server. The program has high performance due to direct connection to data source and destination databases (it does not use ODBC or any other middleware software). Command line support allows to script, automate and schedule the conversion process.<br />
* [http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_SchemaConversionTool.Installing.html/ AWS Schema Conversion Tool] - to convert your existing database schema from one database engine to another (free download).<br />
<br />
=== Documentation, articles, presentations etc. ===<br />
<br />
==== PostgreSQL documentation ====<br />
* [http://www.postgresql.org/docs/current/interactive/plpgsql-porting.html Porting from Oracle PL/SQL]<br />
<br />
==== Articles and presentations (in reverse chronological order) ====<br />
<br />
* [http://tapoueh.org/confs/2013/10/29-pgconfeu-2013 From MySQL to PostgreSQL with pgloader] (English) talk at PostgreSQL Conference Europe, Dimitri Fontaine, 2013-10-29<br />
* [http://wiki.cenatic.es/wikiesp/index.php/Procedimiento_de_An%C3%A1lisis_y_Planificaci%C3%B3n_de_Migraci%C3%B3n_de_Oracle_10g_R2_a_PostgreSQL_9.1.3 Analysis and Planning Process for Migration from Oracle 10g R2 to PostgreSQL 9.1.3] (Spanish) by the Spanish National Competence Center for the Application of Opensource Technologies [http://www.cenatic.es (CENATIC)] (2012-06)<br />
* [http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features] BostonGIS (2010-06-01)<br />
* [http://wiki.kandalaya.org/cgi-bin/twiki/view/Main/MigratingOracleToPostgreSQL Migrating Oracle Databases to PostgreSQL using Xen on Debian GNU/Linux] by Raj Mathur (Kandalaya) (2008-12-26)<br />
* [http://www.pgcon.org/2008/schedule/track/Tutorial/62.en.html Porting Oracle Applications to PostgreSQL] by Peter Eisentraut (PGCon 2008)<br />
* [[PostgreSQL for Oracle DBAs]] by Richard Stephan (2007-06-01)<br />
* [[:Image:Pg 8.1 J2EE v1.0.pdf|Porting JDBC applications from Oracle to PostgreSQL]] by Chris Drawater (2006-03-24)<br />
** [http://www.holindis.co.uk/ Related documents for J2EE, Tomcat, and Oracle migrations] by Chris Drawater (2007-01-15)<br />
* [[Interview with Mark Stosberg (From Oracle/tcl to PostgreSQL/Perl)]] by Robert Treat (2004-10-24)<br />
* [[Oracle to Postgres Conversion]] by James Shannon, Ben Adida, and Don Baccus<br />
<br />
== Progress RDBMS ==<br />
<br />
* [http://knol.google.com/k/tomasz-judycki/convert-progress-rdbms-to-postgresql/2rifd79ifooa9/4# Convert Progress RDBMS to PostgreSQL] by Tomasz Judycki<br />
<br />
== Converting PostgreSQL Databases to other Databases ==<br />
<br />
* [http://docman.sourceforge.net/home_html/projects/sql/pgsql2interbase PostgreSQL to InterBase dump file converter] by Dobrica Pavlinusic<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from PostgreSQL to more than 30 database engines<br />
* [https://github.com/OmniDB/OmniDB OmniDB] Open source web tool for database management and conversion. Currently supports PostgreSQL, Oracle, MariaDB, MySQL, Firebird, SQLite, MS Access, MS SQL Server, MS SQL Compact. OmniDB converts from any supported RDBMS to PostgreSQL and back.<br />
<br />
[[Category:Install]]<br />
[[Category:General articles and guides]]</div>Jkshahhttps://wiki.postgresql.org/index.php?title=Converting_from_other_Databases_to_PostgreSQL&diff=30366Converting from other Databases to PostgreSQL2017-06-08T20:59:41Z<p>Jkshah: /* Utilities, tools, scripts etc. */</p>
<hr />
<div>== Non-specific ==<br />
<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines to PostgreSQL and back<br />
* [https://github.com/dimitri/pgloader pgloader] knows how to load data from MySQL, SQLite, MS SQL Server, dBase files, CSV files and fixed-width data files, and more. Released under The PostgreSQL Licence.<br />
* [https://dbconvert.com/postgresql/ DBConvert PostgreSQL database migration and sync software] Database conversion and synchronization between PostgreSQL/ Amazon RDS, MySQL, MS SQL Server, SQL Azure, Oracle, MS Access.<br />
* [http://www.easyfrom.net/ Converting data between PostgreSQL and others database formats] ESF Database Migration Toolkit enables you to transfer data across various databases, supporting PostgreSQL, MySQL, Oracle, SQL Server, IBM DB2, Informix, Microsoft Access, Microsoft Excel, dBase, Foxpro, Firbird, SQLite etc. - by Martin Williams<br />
* [http://troels.arvin.dk/db/rdbms/ Comparison of different SQL implementations] by Troels Arvin (covers PG 8.4 and MySQL 5.0)<br />
* [[Transactional DDL in PostgreSQL: A Competitive Analysis]] by Greg Smith<br />
* [[Migrating from one database to another with Pentaho ETL]] by Nicola Benaglia<br />
* [http://www.vive.net/products/datapro.htm dataPro] Conversion tool for PostgreSQL, SQLite, MySQL, Oracle, SQL Server and Microsoft Access. Transfer database objects between different databases and servers, convert tables schema and migrate data from one database type to another.<br />
* [http://www.datanamic.com/datadiff-crossdb/ DataDiff CrossDB] is a Windows GUI utility to compare and synchronize/transfer data from PostgreSQL to/from Oracle, MSSQL, MS Access or MySQL databases.<br />
* [http://www.sqlmaestro.com/products/postgresql/datawizard/ PostgreSQL Data Wizard] is a Windows GUI utility to transfer both schema and data from any ADO-compatible source (like MS Access, MySQL, SQL Server, Oracle, etc) to PostgreSQL.<br />
* [https://metacpan.org/module/SQL::Translator SQL::Translator] is a Perl module for translating table definitions between different software.<br />
* [[Foreign_data_wrappers|Foreign data wrappers]] may be useful for exporting data from other databases<br />
* [http://www.convert-in.com/pgskit.htm Postgres Migration Toolkit] Software pack to convert Oracle, MySQL, SQL Server and FoxPro to PostgreSQL, and vice versa.<br />
* [https://github.com/ei-grad/sqlacrossover sqlacrossover] SQLAlchemy-based cross-database migration tool<br />
* [https://skyvia.com Skyvia] Web service for cloud data integration for PostgreSQL with Salesforce, Dynamics CRM, SugarCRM, Zoho CRM, QuickBooks, FreshBooks, ExactTarget, MailChimp, Bigcommerce, MySQL, SQL Server, SQL Azure, Amazon RDS.<br />
* [https://github.com/OmniDB/OmniDB OmniDB] Open source web tool for database management and conversion. Currently supports PostgreSQL, Oracle, MariaDB, MySQL, Firebird, SQLite, MS Access, MS SQL Server, MS SQL Compact. OmniDB converts from any supported RDBMS to PostgreSQL and back.<br />
<br />
== Apache Derby ==<br />
<br />
* [https://github.com/billrobertson42/derby2pg derby2pg] Converter program that will convert the tables, data and indexes in a given schema to a script that can be used to populate a PostgreSQL database.<br />
<br />
== DBase II, III, IV+ / DBF Format ==<br />
<br />
* [http://www.convert-in.com/dbf2pgs.htm FoxPro-to-PostgreSQL] a program to migrate FoxPro databases to PostgreSQL server. The program does not use ODBC or any other middleware software. Command line support allows to script, automate and schedule the conversion process.<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from DBF (dBase, Clipper, Visual FoxPro and other DBF variants) to PostgreSQL and back<br />
* [https://github.com/dimitri/pgloader pgloader] by Dimitri Fontaine<br />
* [http://www.tv.com.pl/stepbystep/dbasepsql/ Convert .dbf files into PostgreSQL] by Tomasz Judycki<br />
* [[:Image:26.zip|A Visual Basic utility to convert Dbase III, IV, and V]] by Dennis Bazan<br />
* [[Porting data from dBASE IV to PostgreSQL]] by Vijay Deval (2002-09-07)<br />
* [https://github.com/kstrauser/pgdbf PgDBF : Simplified and optimized replacement for XBaseToPg] by Kirk Strauser<br />
<br />
== FileMaker Pro ==<br />
<br />
* [[Porting from FileMaker Pro to PostgreSQL]] by Michelle Murrain (24th August 2001)<br />
<br />
== IBM DB2 ==<br />
<br />
* [[:Image:DB2UDB-to-PG.pdf|Migrating from DB2 to PostgreSQL]]<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including IBM DB2, to PostgreSQL and back<br />
* [https://github.com/dalibo/db2topg/ db2topg] Migration tool to convert a DB2 UDB Database into a PostgreSQL database<br />
<br />
== Interbase ==<br />
<br />
* [https://github.com/kstrauser/dbreplicate DBReplicate - Simplify Interbase->PostgreSQL conversions] by Kirk Strauser<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including Interbase, to PostgreSQL and back<br />
<br />
== Microsoft Access ==<br />
<br />
* [http://www.postgresonline.com/journal/index.php?url=archives/346-Querying-MS-Access-and-other-ODBC-data-sources-with-OGR_FDW.html Linking to MS Access tables with ogr_fdw] If your PostgreSQL is on windows, you can use ogr_fdw foreign data wrapper, packaged with PostGIS 2.2+ bundle for windows via application stackbuilder. With PostgresQL 9.5, you can use IMPORT FOREIGN SCHEMA to link in all the MS access tables and then cherry pick how you want to restructure.<br />
* [http://docman.sourceforge.net/home_html/projects/sql/exportSQL3.txt exportSQL] - a Microsoft Access module which exports Access Database into MySQL, mSQL and PostgreSQL by Dobrica Pavlinusic. Based on the work of Pedro Freire<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including Access, to PostgreSQL and back<br />
* [http://mdbtools.sourceforge.net/ MDB Tools] by Brian Bruns<br />
** A quick way to dump all tables as tsv or csv files<br />
for TT in $(mdb-tables file.mdb); do<br />
mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.tsv"<br />
done<br />
<br />
for TT in $(mdb-tables file.mdb); do<br />
mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"<br />
done<br />
<br />
If the tablenames have embedded spaces...<br />
<br />
mdb-tables -1 file.mdb| while read TT<br />
do<br />
mdb-export -D '%Y-%m-%d %H:%M:%S' file.mdb "$TT" > "${TT}.csv"<br />
done<br />
<br />
A shell script that may be useful for converting entire databases:<br />
<br />
#!/bin/sh -e<br />
<br />
mdbfn=$1<br />
schemafn=$2<br />
fkfn=$3<br />
datafn=$4<br />
schema=$5<br />
<br />
tf=$(tempfile)<br />
<br />
pre=""<br />
[ -n "${schema}" ] && pre="\"${schema}\"."<br />
<br />
mdb-schema "${mdbfn}" postgres > "${tf}"<br />
<br />
# Schema file<br />
echo "BEGIN;\n" > "${schemafn}"<br />
<br />
sp=""<br />
[ -n "${schema}" ] && echo "CREATE SCHEMA \"${schema}\";\n" >> "${schemafn}"<br />
[ -n "${schema}" ] && sp="SET search_path = \"${schema}\", pg_catalog;\n" <br />
<br />
echo ${sp} >> "${schemafn}"<br />
<br />
awk '($0 !~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${schemafn}"<br />
<br />
echo "\nEND;" >> "${schemafn}"<br />
<br />
# Foreign keys file<br />
echo "BEGIN;\n" > "${fkfn}"<br />
echo ${sp} >> "${fkfn}"<br />
<br />
awk '($0 ~ /^ALTER TABLE.*FOREIGN KEY.*REFERENCES/) {print;}' "${tf}" >> "${fkfn}"<br />
<br />
echo "\nEND;" >> "${fkfn}"<br />
<br />
# Data file<br />
echo "BEGIN;\n" > "${datafn}"<br />
echo "SET CONSTRAINTS ALL DEFERRED;\n" >> "${datafn}"<br />
<br />
mdb-tables -1 "${mdbfn}" | while read TT<br />
do<br />
mdb-export -Q -d '\t' -D '%Y-%m-%d %H:%M:%S' "${mdbfn}" "$TT" > "${tf}"<br />
<br />
awk -v pre="${pre}" -v TT="${TT}" \<br />
'(NR==1) {gsub(/\t/,"\",\""); print "COPY " pre "\"" TT "\"(\"" $0 "\") FROM stdin;";}' "${tf}" >> "${datafn}"<br />
awk '(NR>1) {gsub(/\t\t/,"\t\\N\t"); gsub(/\t$/,"\t\\N"); gsub(/\t\t/,"\t\\N\t"); print;}' "${tf}" >> "${datafn}"<br />
<br />
echo "\\.\n" >> "${datafn}"<br />
done<br />
<br />
echo "END;" >> "${datafn}"<br />
<br />
rm -f "${tf}"<br />
<br />
If this script is saved to the file access2psql.sh and made executable, then it would be used as follows:<br />
<br />
access2psql.sh file.mdb schema.sql foreignkeys.sql data.sql pg_schema_name<br />
psql -f schema.sql pg_db_name<br />
psql -f data.sql pg_db_name<br />
psql -f foreignkeys.sql pg_db_name<br />
<br />
This script won't work properly if there are tab characters in text columns, though the call to mdb-export could be modified to export INSERT statements to fix this. Also, mdb-schema has trouble representing multi-column foreign keys, so foreignkeys.sql may need some manual editing.<br />
<br />
* [http://convertdb.com/access/postgresql/ Export Microsoft Access to PostgreSQL. Cross database synchronization]<br />
* [http://www.olschimke.eu/2012/08/07/importing-microsoft-access-mdb-into-postgresql-on-linux-postgres/ Importing Microsoft Access MDB into PostgreSQL on Linux] by Michael Olschimke<br />
* [[Microsoft Access to PostgreSQL Conversion]] by Jon Hutchings (2001-07-20)<br />
* [http://pgfoundry.org/projects/access2pgsql/ access2pgsql tool] by Mariano Reingart<br />
* [http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html Using MS Access as front end and PostgreSQL as back-end database] by PostgresOnLine Journal - 2008-01-31<br />
<br />
== Microsoft SQL Server ==<br />
<br />
* [http://www.convert-in.com/mss2pgs.htm MSSQL-to-PostgreSQL] is a migration utility to convert SQL Server or SQL Azure databases to PostgreSQL. Option to filter data using SELECT-queries, synchronization mode, command line support.<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines, including SQL Server, to PostgreSQL and back<br />
* [http://dalibo.github.io/sqlserver2pgsql/ sqlserver2pgsql] Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database<br />
* [https://github.com/dimitri/pgloader pgloader] by Dimitri Fontaine<br />
* [https://dbconvert.com/mssql/postgresql/ DBConvert for PostgreSQL and MS SQL Server] Conversion and Sync software solutions for cross database migration between PostgreSQL/ Amazon RDS and MS SQL Server/ SQL Azure<br />
* [http://sqlblog.com/blogs/alexander_kuznetsov/archive/tags/PostgreSql/default.aspx Learning PostgreSQL (Alexander Kuznetsov)] - series of blog articles for SQL Server users (2013-10 ~ 2013-11)<br />
* [http://www.postgresonline.com/journal/archives/219-SQL-Server-to-PostgreSQL-Converting-table-structure.html Converting SQL Server Table Structure to PostgreSQL] Leo Hsu and Regina Obe (2011-09-03)<br />
* [[:Image:5.pdf|Conversion of Microsoft SQL/ASP applications to PostgreSQL]] by Ethan Townsend (2005-06-23)<br />
* [[Converting your data from MS SQL Server 7 to PostgreSQL 7.1.x]] by Ryan C. Bonham (2002-01-05)<br />
* [[Microsoft SQL Server to PostgreSQL Migration by Ian Harding]] (2001-09-17)<br />
* [http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features] BostonGIS (2010-06-01) <br />
* [http://www.postgresonline.com/journal/index.php?/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html Cross Compare of SQL Server 2005, MySQL 5, and PostgreSQL 8.3] by Leo Hsu and Regina Obe (2008-05-13)<br />
* [http://www.postgresonline.com/journal/index.php?/archives/130-Cross-Compare-of-PostgreSQL-8.4,-SQL-Server-2008,-MySQL-5.1.html Cross Compare of SQL Server 2008, MySQL 5.1, and PostgreSQL 8.4] by Leo Hsu and Regina Obe (2009-08-15)<br />
* [http://www.postgresonline.com/journal/archives/179-Universal-Unique-Identifiers-PostgreSQL-SQL-Server-Compare.html Universal Unique Identifiers PostgreSQL SQL Server Compare] Demonstrates how to implement SQL Server NEWID() GUID uniqueidentifier type in PostgreSQL by Leo Hsu and Regina Obe (2010-10-07)<br />
<br />
== MySQL ==<br />
<br />
<br />
=== Scripts, programs ===<br />
<br />
[https://github.com/dimitri/pgloader pgloader] supports full migration of the database schema and data from a single command line and provides a command language for more complex or specific scenarios. It is still fully supported: please report any bugs on its GitHub page. It appears in the 2013 section here because that's when it has been published first.<br />
<br />
<br />
==== 2015 ====<br />
* [http://www.convert-in.com/sql2pgs.htm MySQL-to-PostgreSQL] is a program to migrate MySQL databases to PostgreSQL server. Option to filter data using SELECT-queries, synchronization mode, command line support.<br />
* [https://github.com/AnatolyUss/FromMySqlToPostgreSql FromMySqlToPostgreSql migration tool by Anatoly Khaytovich], provides an accurate migration of table data, indices, PKs, FKs... Makes an extensive use of PostgreSQL COPY protocol.<br />
<br />
==== 2014 ====<br />
<br />
* [https://github.com/mihailShumilov/mysql2postgresql MySQL/PostgreSQL Converter from Mihail Shumilov], written on PHP. Supported big DB<br />
<br />
==== 2013 ====<br />
* [https://github.com/lanyrd/mysql-postgresql-converter MySQL/PostgreSQL Converter from Lanyrd], based on [http://lanyrd.com/blog/2012/lanyrds-big-move/ work detailed here]<br />
* [https://github.com/dimitri/pgloader pgloader], from Dimitri Fontaine, based on [http://tapoueh.org/blog/2013/01/28-pgloader-future work detailed here] and [http://tapoueh.org/blog/2013/11/12-migrating-sakila Migrating Sakila from MySQL to PostgreSQL]<br />
* [https://github.com/philipsoutham/py-mysql2pgsql py-mysql2pgsql], [https://github.com/mozilla/airmozilla/pull/61/files#diff-30 example use case here]<br />
* [https://github.com/bdigital/my_etl MyETL], Tool that works with the [https://github.com/bdigital/mysql_fdw MySQL FDW]<br />
<br />
==== Previously ====<br />
* [http://www.enterprisedb.com/products/download.do Free MySQL to Postgres Migration Wizard by EnterpriseDB v1.1] by EnterpriseDB Corporation. Available without registration via PostgreSQL installer wizard.<br />
* [http://www.pgsql.com/download/ Conversion tool for migrating from MySQL to PostgreSQL] by PostgreSQL Inc.<br />
* [http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL Converting MySQL to PostgreSQL] - from en.wikibooks.org<br />
* [http://convertdb.com/mysql/postgresql/ Migrate MySQL to PostgreSQL.] - Cross database bidirectional synchronization<br />
* [http://www.sourcefiles.org/Databases/Utilities/Conversion/my2pg.pl my2pg.pl] - A Perl script used to convert a MySQL database dump to PostgreSQL-compatible format, by Maxim Rudensky and Valentine Danilchuk<br />
* [http://pgfoundry.org/projects/mysql2pgsql/ mysql2pgsql] - A Perl script used to convert MySQL databases dump to a PostgreSQL-compatible format<br />
* [http://mp2p.mikekohn.net/ MySQL PHP to PostgreSQL] by Michael Kohn<br />
* [http://www.gab.lc/script_php_my2pg.php PHP_my2pg] PHP script by Gabriel Bordeaux<br />
* [http://tryolabs.com/Blog/2012/02/10/django-migrating-mysql-postgresql/ Migrating from MySQL to Postgresql with Django] blogpost by alejandro<br />
* [http://github.com/maxlapshin/mysql2postgres Migrates MySQL to PostgreSQL] Ruby script by Max Lapshin<br />
# To install mysql2psql (under ubuntu 11.10): No need to get from github, just:<br />
sudo apt-get install ruby gems libmysqlclient-dev libpq-dev<br />
gem install mysql pg mysql2psql<br />
# To get info about the mysql socket:<br />
netstat -l | grep mysql<br />
mysql2psql # creates a .yml templae<br />
vi mysql2psql.yml # edit the template<br />
mysql2psql # connects to mysql database and write into postgres database<br />
<br />
* [http://pypi.python.org/pypi/py-mysql2pgsql py-mysql2pgsql] Python script to convert MySQL database to PostgreSQL compatible file or export directly to a PostgreSQL server<br />
* [https://bitbucket.org/iceone/mysql2pgsql/ my2pg.py] MySQL to PostgreSQL database conversion (not the same as above)<br />
* [http://phpseclib.sourceforge.net/sql/ SQL Data Definition Language (DDL) Conversion to MySQL, PostgreSQL and MS-SQL] by phpBB Group<br />
<br />
=== Documentation ===<br />
<br />
* [http://www.sitepoint.com/article/site-mysql-postgresql-1 Backend Database Switcheroo Howto Part I] by Nathan Matias<br />
* [http://www.sitepoint.com/article/site-mysql-postgresql-2 Backend Database Switcheroo Howto Part II] by Nathan Matias<br />
* [http://www.xach.com/aolserver/mysql-to-postgresql.html How-To: Migrating from MySQL to PostgreSQL] by Zach Beane<br />
* [[Things to find out about when moving from MySQL to PostgreSQL]] by Joel Burton (8th April 2001)<br />
* [[Why PostgreSQL Instead of MySQL: Comparing Reliability and Speed in 2007]] by Greg Smith<br />
* [http://www.postgresonline.com/journal/index.php?/archives/51-Cross-Compare-of-SQL-Server,-MySQL,-and-PostgreSQL.html Cross Compare of SQL Server, MySQL, and PostgreSQL] by Leo Hsu and Regina Obe (2008-05-13)<br />
* [[How to make a proper migration from MySQL to PostgreSQL]]<br />
* [http://andreas.scherbaum.la/writings/migrate_web_platforms.pdf Migrating a community platform from Mysql to PostgreSQL] by Andreas Scherbaum (Prato, 2007)<br />
* [http://okbob.blogspot.com/2009/08/mysql-functions-for-postgresql.html MySQL functions for PostgreSQL] by Pavel Stěhule<br />
<br />
== Oracle ==<br />
<br />
=== Utilities, tools, scripts etc. ===<br />
<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from more than 30 database engines to PostgreSQL and back<br />
* [https://dbconvert.com/oracle/postgresql/ Database migration for Oracle and PostgreSQL] Software for cross - database conversion and sync between Oracle and PostgreSQL.<br />
* [http://ora2pg.darold.net/ Ora2Pg - Oracle to PostgreSQL database schema converter] by Gilles Darold<br />
* [http://oracle-fdw.projects.pgfoundry.org/ PostgreSQL Foreign Data Wrapper for Oracle (oracle_fdw)] - an [[Fdw|FDW]] providing support to access Oracle databases from within PostgreSQL<br />
* [http://orafce.projects.pgfoundry.org/ Orafce] - implements common Oracle functions in PostgreSQL for compatibility<br />
* [http://www.convert-in.com/ora2pgs.htm Oracle-to-PostgreSQL] - a program to migrate Oracle databases to PostgreSQL server. The program has high performance due to direct connection to data source and destination databases (it does not use ODBC or any other middleware software). Command line support allows to script, automate and schedule the conversion process.<br />
* [http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_SchemaConversionTool.Installing.html/ AWS Schema Conversion Tool] - to convert your existing database schema from one database engine to another (free download).<br />
* [http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_SchemaConversionTool.Installing.html/ AWS Schema Conversion Tool] - Free tool from AWS to convert your existing database schema from one database engine to another.<br />
<br />
=== Documentation, articles, presentations etc. ===<br />
<br />
==== PostgreSQL documentation ====<br />
* [http://www.postgresql.org/docs/current/interactive/plpgsql-porting.html Porting from Oracle PL/SQL]<br />
<br />
==== Articles and presentations (in reverse chronological order) ====<br />
<br />
* [http://tapoueh.org/confs/2013/10/29-pgconfeu-2013 From MySQL to PostgreSQL with pgloader] (English) talk at PostgreSQL Conference Europe, Dimitri Fontaine, 2013-10-29<br />
* [http://wiki.cenatic.es/wikiesp/index.php/Procedimiento_de_An%C3%A1lisis_y_Planificaci%C3%B3n_de_Migraci%C3%B3n_de_Oracle_10g_R2_a_PostgreSQL_9.1.3 Analysis and Planning Process for Migration from Oracle 10g R2 to PostgreSQL 9.1.3] (Spanish) by the Spanish National Competence Center for the Application of Opensource Technologies [http://www.cenatic.es (CENATIC)] (2012-06)<br />
* [http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features] BostonGIS (2010-06-01)<br />
* [http://wiki.kandalaya.org/cgi-bin/twiki/view/Main/MigratingOracleToPostgreSQL Migrating Oracle Databases to PostgreSQL using Xen on Debian GNU/Linux] by Raj Mathur (Kandalaya) (2008-12-26)<br />
* [http://www.pgcon.org/2008/schedule/track/Tutorial/62.en.html Porting Oracle Applications to PostgreSQL] by Peter Eisentraut (PGCon 2008)<br />
* [[PostgreSQL for Oracle DBAs]] by Richard Stephan (2007-06-01)<br />
* [[:Image:Pg 8.1 J2EE v1.0.pdf|Porting JDBC applications from Oracle to PostgreSQL]] by Chris Drawater (2006-03-24)<br />
** [http://www.holindis.co.uk/ Related documents for J2EE, Tomcat, and Oracle migrations] by Chris Drawater (2007-01-15)<br />
* [[Interview with Mark Stosberg (From Oracle/tcl to PostgreSQL/Perl)]] by Robert Treat (2004-10-24)<br />
* [[Oracle to Postgres Conversion]] by James Shannon, Ben Adida, and Don Baccus<br />
<br />
== Progress RDBMS ==<br />
<br />
* [http://knol.google.com/k/tomasz-judycki/convert-progress-rdbms-to-postgresql/2rifd79ifooa9/4# Convert Progress RDBMS to PostgreSQL] by Tomasz Judycki<br />
<br />
== Converting PostgreSQL Databases to other Databases ==<br />
<br />
* [http://docman.sourceforge.net/home_html/projects/sql/pgsql2interbase PostgreSQL to InterBase dump file converter] by Dobrica Pavlinusic<br />
* [https://www.spectralcore.com/fullconvert Full Convert] Database conversion from PostgreSQL to more than 30 database engines<br />
* [https://github.com/OmniDB/OmniDB OmniDB] Open source web tool for database management and conversion. Currently supports PostgreSQL, Oracle, MariaDB, MySQL, Firebird, SQLite, MS Access, MS SQL Server, MS SQL Compact. OmniDB converts from any supported RDBMS to PostgreSQL and back.<br />
<br />
[[Category:Install]]<br />
[[Category:General articles and guides]]</div>Jkshahhttps://wiki.postgresql.org/index.php?title=PgConfUS_Talks_2017&diff=29750PgConfUS Talks 20172017-04-04T17:54:04Z<p>Jkshah: /* America */</p>
<hr />
<div>= PGConf US 2017 Talks =<br />
<br />
== Conference Website ==<br />
<br />
http://www.pgconf.us/2017/<br />
<br />
== Videos ==<br />
<br />
https://www.youtube.com/pgconfus/<br />
<br />
== Trainings: Tuesday, 28 March 2017 ==<br />
* PostgreSQL When It's Not Your Job, Christophe Pettus<br />
* Designing a Multi-tenant Database for Scale with PostgreSQL, Ozgun Erdogan and Lukas Fittl<br />
* Pacific Crest Trail: The postgresql.conf from A-Z, Joshua D. Drake<br />
* Detecting performance problems and fixing them, Hans-Jürgen Schönig<br />
* Getting Stuff done with PostGIS, Leo Hsu and Regina Obe<br />
* Linux IO internals for PostgreSQL administrators, Ilya Kosmodemiansky<br />
<br />
== Regulated Industry Summit ==<br />
* Creating blockchain platforms and future directions, Ryan Smith<br />
* Blockchain, Distributed Security and Regulated Industries, Chris Dannen<br />
* Public Clouds & Risk Assessments for Regulated Industries, Kai Huang<br />
* Secure Containers, Bridging Mainframes to Cloud, (IBM), Diana Henderson<br />
* Engineering cloud infrastructure for HIPAA-compliance, Shaun Qualheim<br />
* HIPAA Compliant, Scalable and Highly Available PostgreSQL Architecture in AWS, Yaser Raja and Glenn Poston<br />
* How to Make Security on NoSQL More than just an afterthought, Chad Hardin<br />
* Secure Technical Implementation Guide (STIG) For PostgreSQL, Jason O'Donnell<br />
* Security Enhancements in Postgres 10, Simon Riggs<br />
<br />
== Sessions: Wednesday, 29 March 2017 ==<br />
<br />
=== Liberty I ===<br />
* [https://www.joeconway.com/presentations/intro_to_postgresql-pgconf.us-2017.pdf Intro to PostgreSQL - a jumpstart to up and running], Joe Conway<br />
* [http://anarazel.de/talks/pgconf-us-2017-03-29/jit.pdf Speeding up Query Execution], Andres Freund<br />
* [https://speakerdeck.com/peterg/sort-hash-pgconfus-2017 Sort vs. Hash: A Duality], Peter Geoghegan<br />
* In-core compression: how to shrink your database size in several times, Anastasia Lubennikova<br />
* [http://momjian.us/main/writings/pgsql/mvcc.pdf MVCC Unmasked], Bruce Momjian<br />
* Peeking into the Black Hole called PL/pgSQL - The new PL Profiler, Jan Wieck<br />
<br />
=== Liberty II ===<br />
* Designing your SaaS Database for Scale with Postgres, Ozgun Erdogan<br />
* Time in Databases, Abdullah Uz Tansel<br />
* [https://www.slideshare.net/AJBahnken/gophers-riding-elephants-writing-postgresql-tools-in-go Gophers Riding Elephants: Writing PostgreSQL tools in Go], AJ Bahnken<br />
* Migration from Oracle to native PostgreSQL, Max ten Dam<br />
* IDENTIFYING SLOW QUERIES AND FIXING THEM!, Stephen Frost<br />
* Tour de Data Types: VARCHAR2 or CHAR(255)?, Andreas Scherbaum<br />
<br />
=== Liberty III ===<br />
* Analytics Infrastructure Powered by Logical Decoding and Kafka, Jeff Klukas<br />
* Lessons Learned the Hard Way: Postgres in Production at GoCardless, Chris Sinjakli<br />
* Designing The Right Schema To Power Heap, Dan Robinson<br />
* Softlayer vs RDS/AWS: A price to performance perspective, Joshua D. Drake<br />
* Transitioning from MSSQL to PostgreSQL, our trails and tribulations, Louis Royster<br />
* Pgcrypto avast!: A case-study in Django's password hashers, Drew Engelson<br />
<br />
=== America ===<br />
<br />
* Enterprise Data Architecture with PostgreSQL, Kevin Kempter<br />
* POWER Platform - The New Home For PostgreSQL, Michael Meskes<br />
* [http://www.slideshare.net/PostgreSQL-Consulting/postgresql-worst-practices-version-pgconfus-2017-by-ilya-kosmodemiansky PostgreSQL worst practices], Ilya Kosmodemiansky<br />
* Patroni - HA PostgreSQL made easy, Alexander Kukushkin<br />
* High-Availability Postgres with etcd, Tess Rinearson<br />
* DevOps Tools for managing PostgreSQL, Kunal Jain,Yun Guo<br />
<br />
== Sessions: Thursday, 30 March 2017 ==<br />
<br />
=== Liberty I ===<br />
* Logical Replication in PostgreSQL 10, Peter Eisentraut<br />
* [http://www.sai.msu.su/~megera/postgres/talks/jsonb-pgconf.us-2017.pdf Jsonb in Postgres - the present and future], Oleg Bartunov<br />
* All the dirt on VACUUM: 9.6 edition, Jim Nasby<br />
* Next-Generation Parallel Query, Robert Haas<br />
* Collaboration and Community in PostgreSQL, Debra Cerda<br />
* Data Rescue, Debra Cerda,Jim Nasby<br />
<br />
=== Liberty II ===<br />
* Google Workshop: Cloud SQL, Alexis Guajardo<br />
* Schema management with Flyway, Scott Mead<br />
* [https://github.com/dwsteele/conference/blob/release/AuditLogging-PGConfUS-2017/slides.pdf Audit Logging for PostgreSQL], David Steele<br />
* Migrating off of MongoDB to PostgreSQL, Alvaro Hernandez<br />
* Turbocharge your SQL queries in-memory with Apache Ignite, Denis Magda<br />
* OpenSCG Workshop: Hands on Oracle to PostgreSQL Migration, David Rader,Scott Mead,Jan Wieck,Denis Lussier<br />
<br />
=== Liberty III ===<br />
* The dark side of the universe from Oracle to PostgreSQL, Sara Nieto<br />
* Top 10 Problems Solved by PostGIS, Leo Hsu and Regina Obe<br />
* Processing 1 billion rows per second, Hans Jürgen Schönig<br />
* Corruption War Stories, Christophe Pettus<br />
* EDB Workshop: 5 Things Developers Need to Know About Digital Transformation with Postgres, Bruce Momjian<br />
<br />
=== America ===<br />
* LXD, the Container Hypervisor, Anthony Davis<br />
* Columns and Clouds - Hybrid databases, David Rader<br />
* [http://jkshah.blogspot.com/2017/03/pgconf-2017-postgresql-high.html PostgreSQL High Availability in a Containerized World], Jignesh Shah<br />
* From Dev to prod, The ABC's of PostgreSQL backups, Scott Mead<br />
* Amazon RDS for PostgreSQL: Whatâs New and Lessons Learned, Grant McAlister<br />
* Automating Production-Ready Databases in Docker, Jesse White<br />
<br />
=== Enterprise ===<br />
* DataGrip, SQL client from JetBrains, Vasiliy Chernov<br />
* Python + Jupyter + Postgres, Jim Nasby<br />
* Postgres Performance for Application Developers, Lukas Fittl<br />
* Django and PostgreSQL: Two Great Tastes, Christophe Pettus<br />
* An Introduction to SQLAlchemy and Friends, Jon Banafato<br />
<br />
== Sessions:Friday, 31 March 2017 ==<br />
<br />
=== Liberty I ===<br />
* 2ndQuadrant Workshop: Logical replication deep dive, Peter Eisentraut<br />
* a Practical Postgres Protocol Primer, Will Leinweber<br />
* PostgreSQL BI Performance & News from XL project, Tomas Vondra<br />
* Implementing Graph Database based on PostgreSQL, Kisung Kim<br />
* Evolution of pgAdmin: pgAdmin4, Dave Page<br />
<br />
=== Liberty II ===<br />
* ETL Confessions, Corey Huinker<br />
* RDS or: How I Learned to Stop Worrying and Love the Cloud, Gavin M. Roy<br />
* [https://www.joeconway.com/presentations/plr-spatial-pgconf.us-2017.pdf How Green Was My Valley - Spatial Analytics], Joe Conway<br />
* Exploiting the hidden talents of JDBC for maximum performance, Dave Cramer<br />
<br />
=== Liberty III ===<br />
* TOASTing an Elephant : Building a Custom Data Warehouse Using PostgreSQL, David Kohn<br />
* 600k queries per second in PostgreSQL - we do it in TomTom, Michał Gutkowski<br />
* [http://slides.keithf4.com/managing_pg_packages Managing OS Provided PostgreSQL Packages], Keith Fiske<br />
* Advanced Postgres Monitoring, Denish Patel<br />
<br />
=== America ===<br />
* Restrictions on PostgreSQL Cloud DBaaS, Simon Riggs<br />
* Provisioning and Automating High Availability Postgres on AWS EC2, Payal Singh<br />
* TimescaleDB: Designing a scalable time-series database on PostgreSQL, Mike Freedman</div>Jkshahhttps://wiki.postgresql.org/index.php?title=PostgreSQL_Conference_Europe_Talks_2013&diff=21166PostgreSQL Conference Europe Talks 20132013-11-04T16:56:24Z<p>Jkshah: /* Leeson */</p>
<hr />
<div>= PostgreSQL Conference Europe 2013 Talks =<br />
<br />
== Conference Website ==<br />
<br />
http://2013.pgconf.eu/<br />
<br />
<br />
== Trainings: Tuesday 29th October, 2013 ==<br />
<br />
=== Dargle ===<br />
<br />
* PostgreSQL & PostGIS (Gianni Ciolli)<br />
* Writing & using Postgres Extensions (Dimitri Fontaine)<br />
<br />
=== Dodder ===<br />
<br />
* PostgreSQL indexing (Hans-Jürgen Schönig)<br />
<br />
=== Liffey ===<br />
<br />
* PostgreSQL Performance Tuning (Bruce Momjian)<br />
* Data Processing Inside PostgreSQL (Bruce Momjian)<br />
<br />
<br />
== Talks: Wednesday 30th October, 2013 ==<br />
<br />
=== Earlsfort ===<br />
<br />
* Keynote: Handling the Spotlight: How PostgreSQL can become the database of choice (Mark Taylor)<br />
* What's New in PostgreSQL 9.3 (Robert Treat)<br />
* [http://gauss.credativ.com/~mme/2013/PGConf_EU_2013_V01.pdf PostgreSQL - One of the most important Open Source projects] (Michael Meskes)<br />
* [https://github.com/Oslandia/presentations/tree/master/pgconf_eu_2013 Going spatial with PostGIS and more] (Hugo Mercier)<br />
* [http://momjian.us/main/presentations/features.html#nulls Nulls Make Things Easier?] (Bruce Momjian)<br />
* [http://www.slideshare.net/GabrieleBartolini/postgresql-disaster-recovery-with-barman-pgconfeu-2013 PostgreSQL Disaster Recovery with Barman] (Gabriele Bartolini)<br />
<br />
=== Leeson ===<br />
<br />
* [http://www.hesotech.de/talks/PgEu-Dublin-2013.pdf PostgreSQL for Rails] (Roland Sonnenschein)<br />
* Introduction of pg_statsinfo and pg_stats_reporter ~Statistics Reporting Tool for DBA~ (Mitsumasa KONDO)<br />
* [http://de.slideshare.net/psoo1978/pg-fdw Writing a foreign data wrapper] (Bernd Helmle)<br />
* [http://de.slideshare.net/aldaschwede80/sybase-topostgres-en Success stories on migrating from Sybase ASE to PostgreSQL] (Achim Eisele, Jens Wilke)<br />
* [http://www.dalibo.org/_media/multicorn/pgday.eu.2013.multicorn_writing_foreign_data_wrappers_in_python.pdf Multicorn: writing Foreign Data Wrappers in python] (Ronan Dunklau)<br />
<br />
=== Pembroke ===<br />
<br />
* [[Media:PGConf-EU-2013-User-Defined-Datatype.pdf| Writing a user-defined datatype]] ([[Media:Pgconf-eu-2013-example.tar.gz|sources for the example]]) (Heikki Linnakangas)<br />
* LATERAL querying in 9.3; keep up with the new features! (Gianni Ciolli)<br />
* Detecting performance bottlenecks (Hans-Jürgen Schönig)<br />
* [http://www.slideshare.net/jkatz05/explain-the-index-of-postgresql-indexes Explaining the Index of PostgreSQL Indexes] (Jonathan S. Katz)<br />
* [http://www.dalibo.org/_media/pgday.eu.2013.conduct_change_from_oracle_to_postgresql.pdf Conduct change from Oracle to PostgreSQL] (Jean-Paul Argudo)<br />
<br />
== Talks: Thursday 31th October, 2013 ==<br />
<br />
=== Earlsfort ===<br />
<br />
* Taking advantage of custom background workers (Michael Paquier)<br />
* Doing PITR Right - There's more than just replication (Stephen Frost)<br />
* [https://speakerdeck.com/craigkerstiens/postgres-what-they-really-use slides Postgres what they really use] ([http://www.craigkerstiens.com Craig Kerstiens])<br />
* [http://www.slideshare.net/MarkusWinand/indexes-neglectedperformanceallrounder Indexes: The neglected performance all-rounder ]([http://winand.at/ Markus Winand])<br />
* Migrating from MySQL to PostgreSQL (Dimitri Fontaine)<br />
* Lightning talks (Harald Armin Massa)<br />
<br />
=== Leeson ===<br />
<br />
* Gotcha! Finer points of the postgres query planner and how to use them in your queries (Atri Sharma)<br />
* [https://wiki.postgresql.org/images/a/ad/Materialised_views_now_and_the_future-pgconfeu_2013.pdf Materialised views now and the future] (Thom Brown)<br />
* [http://www.slideshare.net/davidfetter/federation-with-foreigndatawrapperspgconfeu20131031 Federation with Foreign Data Wrappers] (David Fetter)<br />
* [http://prezi.com/uoa2jvfutpy9/?utm_campaign=share&utm_medium=copy&rc=ex0share Switching Horses Overnight - a Software Manufacturer's View on Replacing an Existing Database Management System with PostgreSQL] (Robert Lichtenberger, Stefan Andreatta)<br />
* [http://jkshah.blogspot.com/2013/10/my-experience-with-embedding-postgresql.html My experience with embedding PostgreSQL] (Jignesh Shah)<br />
<br />
=== Pembroke ===<br />
<br />
* [http://www.dalibo.org/_media/pgconf.eu.2013.conferences-pgbadger_v.4.pdf pgBadger v.4] (Jean-Paul Argudo)<br />
* Business Intelligence & Performance (Simon Riggs)<br />
* [https://speakerdeck.com/peterg/concurrency-in-postgres Concurrency in Postgres] (Peter Geoghegan)<br />
* [http://www.sai.msu.su/~megera/postgres/talks/Next%20generation%20of%20GIN.pdf Next generation of GIN] (Alexander Korotkov, Oleg Bartunov)<br />
* [https://speakerdeck.com/leinweber/visualizing-postgres Visualizing Postgres] ([http://bitfission.com Will Leinweber])<br />
<br />
=== Lightning talks ===<br />
<br />
* [http://www.slideshare.net/davidfetter/slides-pg-confeu20131031 Slides] (David Fetter)<br />
* [https://wiki.postgresql.org/images/1/1c/Json-and-speed.pdf JSON & Speed] (Jonathan S. Katz)<br />
* [https://wiki.postgresql.org/images/a/ab/Pganalyze_Lightning_talk.pdf pganalyze - Monitoring for Casual DBAs] (Michael Renner)<br />
* [https://wiki.postgresql.org/images/7/7f/Lightning_100k.pdf 100k Appliances] (Christoph Moench-Tegeder)<br />
<br />
== Talks: Friday 1st November, 2013 ==<br />
<br />
=== Earlsfort ===<br />
<br />
* Epic fails in the RDBMS world (Willem Leenen)<br />
* Using Postgres FDW w/ a sharded PostgreSQL Database (Stephen Frost)<br />
* Postgres versus an anonymous database from Redwood (Gregory Stark)<br />
* Hierarchies Without Masters (Gianni Ciolli)<br />
* PostgreSQL Futures (Simon Riggs)<br />
* PostgreSQL in 5 years - Expectations from the market place (Keith Alsheimer)<br />
* Closing (Dave Page, Magnus Hagander)<br />
<br />
=== Leeson ===<br />
<br />
* [http://www.slideshare.net/MarkusWinand/p2d2-pagination-done-the-postgresql-way Pagination done the PostgreSQL way] ([http://winand.at/ Markus Winand])<br />
* Postgres from Vision to Reality, ABNAMRO Client case (Michel Sijmons)<br />
* PostgreSQL Backups, the good the bad and the ugly (Joshua D. Drake)<br />
* Elephants in fashion (Valentine Gogichashvili)<br />
<br />
=== Pembroke ===<br />
<br />
* [http://www.slideshare.net/GabrieleBartolini/agile-oraclemigrations Agile Oracle to PostgreSQL migrations] (Gabriele Bartolini)<br />
* Useful PostgreSQL Extensions -- the extensions behind the scenes (Devrim GÜNDÜZ)<br />
* [http://www.sai.msu.su/~megera/postgres/talks/hstore-dublin-2013.pdf Binary storage for nested data structures and application to hstore data type] (Oleg Bartunov, Teodor Sigaev)<br />
* Wiretapping the Wire Protocol: automatic data visualization for psql with Cartographer and FEMEBE (Maciek Sakrejda)</div>Jkshahhttps://wiki.postgresql.org/index.php?title=Postgres_Open_2013&diff=21066Postgres Open 20132013-10-31T16:20:01Z<p>Jkshah: add link to Jignesh's presentation</p>
<hr />
<div>Slides for talks will be linked here!<br />
<br />
Events shown for placeholding purposes. See the "Editing help" for wiki formatting tips.<br />
<br />
== Monday September 16 ==<br />
* PostgreSQL Data Type Safari (Jonathan Katz)<br />
* Surviving Server Overload (Greg Smith)<br />
* [https://wiki.postgresql.org/wiki/File:Pgopen2013_Normalization_part_1.ppt Database Normalization for Everybody <Part 1>], [https://wiki.postgresql.org/wiki/File:Pgopen_2013_Normalization_part_2.ppt <Part 2>] (Brent Friedman)<br />
* [https://speakerdeck.com/zzzeek/introduction-to-sqlalchemy-and-orms-pgopen-2013 Introduction to SQLAlchemy (Mike Bayer)]<br />
<br />
== Tuesday September 17 ==<br />
* Keynote (Jesse Noller)<br />
<br />
* [http://www.slideshare.net/denishpatel/scaling-postgres Scaling Postgres (Denish Patel)]<br />
* What's New in PostgreSQL 9.3 (Magnus Hagander)<br />
* [http://t.co/q0dQZJg2cJ Taking Advantage of Custom Background Workers (Michael Pacquier)]<br />
<br />
* [https://sites.google.com/site/robertmhaas/presentations/Query%20Planning%20Gone%20Wrong.odp?attredirects=0&d=1 Query Planning Gone Wrong (Robert Haas)]<br />
* [https://wiki.postgresql.org/wiki/File:Pgopen-Recovery_damaged_cluster(1).pdf Recovering from a Damaged Cluster (Robert Bernier)]<br />
* Middleware is Dead! (David Benoit)<br />
<br />
* [https://speakerdeck.com/selenamarie/sane-schema-management-with-alembic Sane Schema Management With Alembic (Selena Deckelmann)]<br />
* Postgres what they really use (Craig Kersteins)<br />
* [http://sourceforge.net/apps/mediawiki/postgres-xc/index.php?title=PGOpen2013_Postgres_Open_2013 Postgres-XC dynamic cluster management (Koichi Suzuki)]<br />
<br />
* [http://phaedrusdeinus.org/talks/federate_fdw/federate_fdw.pdf Federating Queries Using postgres_fdw (John Melesky)]<br />
* [http://scanningpages.files.wordpress.com/2013/09/puppet_pgopen2013.pdf Managing PostgreSQL with Puppet (Steve Singer)]<br />
* [http://jkshah.blogspot.com/2013/10/my-experience-with-embedding-postgresql.html My experience with embedding PostgreSQL (Jignesh Shah)]<br />
<br />
* [http://speakerdeck.com/peterg/concurrency-in-postgres Concurrency in Postgres (Peter Geoghegan)]<br />
* [https://docs.google.com/file/d/0B2hio52iFB7AaHB0VEhaYmtZMms/edit?pli=1 New approach to resolve Object-relational impedance mismatch (Henrietta Dombrovskaya)]<br />
* [http://baumler.com/postgres/ Case Study: Converting the CATME application from MySQL to Postgres (Julie Baumler)]<br />
<br />
=== Lightning Talks ===<br />
* [http://tinyurl.com/pgopen2013-ipy-sql IPython Notebook: your new SQL client (Catherine Devlin)]<br />
* NoSQL in 5 minutes (Ines Sombra)<br />
* Publish Your Extensions on PGXN (Kirk Roybal)<br />
* Configuration Managment (Mark Mzyk)<br />
* [https://wiki.postgresql.org/images/d/d4/Json-speed-user-groups.pdf JSON, Speed, and User Groups (Jonathan Katz)]<br />
* [https://www.dropbox.com/s/x5qr9rkoemjqgi7/blackhole.pdf Blackhole FDW (Andrew Dunstan)]<br />
* [http://www.slideshare.net/denishpatel/choosing-the-d Choosing the "D"/Doctor (Denish Patel)]<br />
* [http://www.slideshare.net/markwkm/ohai-my-name-is-chelnik-postgres-open-2013-report OHAI, my name is Chelnik!] Postgres Open 2013 Report (Mark Wong)<br />
* New Tools (Greg Stark)<br />
* Postgres Enterprise Manager (Dave Page & Tom Kincaid)<br />
<br />
== Wednesday September 18 ==<br />
* Postgres Plus Cloud Database (Hans Hrasna (Tom Kincaid))<br />
* [http://bit.ly/1eddml6 Writing portable postgreSQL-backed network applications for the open cloud (Ryan Jarvinen)]<br />
* [http://www.jackdb.com/assets/misc/2013-09-18/jackdb-pgopen.pdf JackDB - Database Development In Your Web Browser (Sehrope Sarkuni)]<br />
<br />
* [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Inside PostgreSQL Shared Memory (Bruce Momjian)]<br />
* Geographically Distributed Parallel Databases – A Reality! (Christine Normile)<br />
* [https://wiki.postgresql.org/wiki/File:Postgres_Is_Different_From_Better_Than_Your_RDBMS.pdf How Postgres is different from (read BETTER than) your database (Gurjeet Singh)]<br />
<br />
* Replication Refresh (Simon Riggs)<br />
* [http://www.keithf4.com/wp-content/uploads/2013/09/pg_open2013_when_pg_cant_you_can.pdf When PostgreSQL Can't, You Can (Keith Fiske)]<br />
* Multi-Database Collaboration and Extract-Transform-Load with HTSQL (Clark C. Evans)<br />
<br />
* [https://github.com/fakber/pgopen2013/raw/master/PostgresOpen2013-EncryptionOptions.pdf A Comparison of PostgreSQL Encryption Options (Syed Faisal Akber, Dong Ye)]<br />
* Using Postgres FDW w/ a sharded PostgreSQL Database (Stephen Frost)<br />
* [https://www.rdbhost.com/presentations/postgresopen2013/RdbHost.pptx SQL database server Requests from the Browser (David Keeney)]<br />
<br />
* [https://www.dropbox.com/s/gtxnueq6hc2ycr5/9.3json.pdf Using JSON with Postgres 9.3 (Andrew Dunstan)]<br />
* [https://wiki.postgresql.org/wiki/File:PostgresOnZFS-PGO.pdf PostgreSQL on ZFS: backup, replication, disaster recovery, and more (Keith Paskett)]<br />
* [http://www.consistentstate.com/pdf/backup-and-recovery-methods.pdf PostgreSQL Backup and Recovery Methods (Kevin Kempter)]<br />
<br />
* [https://speakerdeck.com/craigkerstiens/scalable-uniques-in-postgres-with-hll Scalable Uniques in Postgres - HyperLogLog (Craig Kerstiens)]<br />
* NewSQL: Leveraging PostgreSQL (John S. Jones)<br />
* Secure your webapp passwords in PostgreSQL (Magnus Hagander)</div>Jkshahhttps://wiki.postgresql.org/index.php?title=PyCon2013Booth&diff=19036PyCon2013Booth2013-02-13T04:53:13Z<p>Jkshah: </p>
<hr />
<div>= PyCon 2013 Volunteer Booth Schedule =<br />
<br />
This is a draft schedule for booth volunteers at PyCon 2013, for the PostgreSQL booth in the exhibit hall.<br />
<br />
Thursday 4pm to 8pm: Josh Berkus, Quinn Weaver<br />
<br />
Friday 10am to 2pm: Josh Berkus, Jeff Frost<br />
<br />
Friday 1:30pm to 5pm: Jignesh Shah*<br />
<br />
Saturday 10am to 2pm: Josh Berkus,<br />
<br />
Saturday 1:30pm to 5pm: Josh Berkus,<br />
<br />
The Thursday shift includes booth setup and the Vendor Reception. The Saturday afternoon shift includes booth pack-up.<br />
<br />
Please mark yourself with a * if you need to be registered for PyCon as a booth volunteer, because you don't have PyCon registration otherwise.</div>Jkshahhttps://wiki.postgresql.org/index.php?title=PyCon2013Booth&diff=19034PyCon2013Booth2013-02-13T04:48:59Z<p>Jkshah: </p>
<hr />
<div>= PyCon 2013 Volunteer Booth Schedule =<br />
<br />
This is a draft schedule for booth volunteers at PyCon 2013, for the PostgreSQL booth in the exhibit hall.<br />
<br />
Thursday 4pm to 8pm: Josh Berkus, Quinn Weaver<br />
<br />
Friday 10am to 2pm: Josh Berkus, Jeff Frost<br />
<br />
Friday 1:30pm to 5pm: Jignesh Shah<br />
<br />
Saturday 10am to 2pm: Josh Berkus,<br />
<br />
Saturday 1:30pm to 5pm: Josh Berkus,<br />
<br />
The Thursday shift includes booth setup and the Vendor Reception. The Saturday afternoon shift includes booth pack-up.<br />
<br />
Please mark yourself with a * if you need to be registered for PyCon as a booth volunteer, because you don't have PyCon registration otherwise.</div>Jkshahhttps://wiki.postgresql.org/index.php?title=Postgres_Open_2012&diff=18245Postgres Open 20122012-09-19T19:45:04Z<p>Jkshah: /* Wednesday September 19 */ Added DVDStore presentation</p>
<hr />
<div>Slides for talks will be linked here!<br />
<br />
== Tuesday September 18 ==<br />
<br />
* [https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf Range Types in PostgreSQL 9.2 - Your Life Will Never Be the Same]<br />
* Scaling out by distributing and replicating data in Postgres-XC<br />
* PostgreSQL When It's Not Your Job<br />
<br />
* [http://tapoueh.org/images/fotolog.pdf Large Scale MySQL Migration to PostgreSQL]<br />
* Choosing a logical replication system: Slony vs Bucardo<br />
* Temporal Database Demo<br />
<br />
* 12 Calm Years of PostgreSQL in Critical Messaging<br />
* PostgreSQL in the cloud: Theory and Practice<br />
* [http://www.hagander.net/talks/Backup%20strategies.pdf PostgreSQL Backup Strategies]<br />
<br />
* This Is PostGIS<br />
* Embracing the Web with JSON and PLV8<br />
* How Akiban Implemented a New Database Compatible with the PostgreSQL Protocol<br />
<br />
* [[Media:Ha_postgres.pdf|High Availability with PostgreSQL and Pacemaker]]<br />
* Logging: Not Just for Lumberjacks<br />
* [http://stuff.coffeecode.net/2012/pgopen_fulltext/pgsql-fulltext-intro.html Full-text search - seek and ye shall find]<br />
<br />
=== Lightning Talks ===<br />
<br />
TBD!<br />
<br />
== Wednesday September 19 ==<br />
<br />
* [http://pgexperts.com/document.html?id=54 Super Jumbo Deluxe], plus [http://pgexperts.com/presentations.html more presentations here]<br />
* [[Media:OO_approach.pdf|An object oriented approach to data driven software development]]<br />
* TransLattice Elastic Database Architecture Deep Dive<br />
<br />
* [http://www.pateldenish.com/2012/04/deploying-maximum-ha-architecture-with-postgresql.html Deploying maximum HA architecture with Postgres] , for pg_reorg [http://static.slidesharecdn.com/swf/ssplayer2.swf?doc=p90xyourdatabase-13010796913746-phpapp02&stripped_title=p90-x-your-database p90x talk]<br />
* Scaling Postgres with some help from Redis<br />
* Retail DDL<br />
<br />
* Query Logging and Workload Analysis<br />
* A Shared-nothing cluster system: Postgres-XC<br />
* [http://jkshah.blogspot.com/2012/09/pgopen-2012-dvdstore-benchmark-and.html DVDStore Benchmark and PostgreSQL]<br />
<br />
* Performance Improvements in PostgreSQL 9.2<br />
* A Batch of Commit Batching<br />
* [http://momjian.us/main/presentations/features.html#cte Programming the SQL Way with Common Table Expressions]<br />
<br />
* Postgres is the new default – how we transitioned our platform at Engine Yard and why you should too<br />
* Leveraging PLV8 in Javascript-heavy Web Applications<br />
* PG Extractor - A smarter pg_dump</div>Jkshahhttps://wiki.postgresql.org/index.php?title=Postgres_Open_Talks_2011&diff=15450Postgres Open Talks 20112011-09-20T21:18:18Z<p>Jkshah: /* Cotillion Room */</p>
<hr />
<div>= Postgres Open Talks 2011 =<br />
<br />
Here will be the Postgres Open talks. <br />
<br />
== Tutorials: Wednesday September 14, 2011 ==<br />
<br />
=== Mayfair Room ===<br />
* [http://bunsen.credativ.com/~jco/2011/migrating.pdf Migration to PostgreSQL - preparation and methodology]<br />
* [http://www.scribd.com/doc/65304692/Scaling-With-SkyTools Scaling With Skytools]<br />
<br />
=== Governor's Suite ===<br />
* [http://momjian.us/main/writings/pgsql/administration.pdf Mastering PostgreSQL Administration]<br />
<br />
== Sessions: Thursday September 15, 2011 ==<br />
<br />
=== Cotillion Room ===<br />
* [http://www.pgexperts.com/document.html?id=53 Keynote: The next 25 years]<br />
* [http://bonesmoses.org/presentations/nvram_fun_profit.pdf NVRAM for Fun and Profit]<br />
* [http://portal.sliderocket.com/vmware/PGOpen2011_VirtEnv Running PostgreSQL on Virtualized Environments - Jignesh Shah]<br />
* [http://wiki.postgresql.org/wiki/File:Pg_query_analysis_20110914.odp Identifying Slow Queries and Fixing Them Slides]<br />
<br />
=== Mayfair Room ===<br />
* [http://joshwilliams.name/talks/monitoring/ Monitoring the Heck out of Your Database]<br />
* [http://www.slideshare.net/DBNess/honey-i-shrunk-the-database-9273383 Honey, I Shrunk the Database]<br />
* [http://wiki.postgresql.org/images/0/0b/Postgresql_django_extensions.pdf Writing Django Extensions for PostgreSQL]<br />
* [http://wiki.postgresql.org/images/8/8e/Logging.odp Logging - Not Just for Lumberjacks] (speaker notes included)<br />
* [http://www.pgexperts.com/presentations.html PostgreSQL 9.1 Grand Tour]<br />
<br />
=== Governors' Suite ===<br />
<br />
* [http://heroku-postgres-talk.heroku.com/ Heroku Postgres]<br />
* [http://wiki.postgresql.org/images/5/58/Why_you_should_not_move_away_from_Oracle.pdf Why you should NOT move away from Oracle]<br />
* [http://bunsen.credativ.com/~mme/2011/MissionImpossible.pdf Mission impossible? Can I replace my most important databases with PostgreSQL?]<br />
* [http://wiki.postgresql.org/images/0/0c/BillingSystemForTELCO_technical.pdf Billing System for a TELCO based on PostgreSQL - technical description]<br />
* [[media:HP_Postgres_Insight_RS.pdf|HP Performs Large-scale Deployment of Postgres with its Remote Support Software]]<br />
<br />
=== Michigan Demos ===<br />
<br />
* [http://www.enterprisedb.com/resources-community/webcasts-podcasts-videos/webcasts/postgres-cloud-introduction-postgres-plus-clou Postgres In the Cloud: an Introduction to Postgres Plus Cloud Server]<br />
<br />
== Sessions: Friday September 16, 2011 == <br />
<br />
<br />
=== Cotillion Room ===<br />
<br />
* [http://wiki.postgresql.org/images/d/d3/Unlogged_Tables_Postgres_Open.odp Unlogged Tables]<br />
* [http://momjian.us/main/writings/pgsql/locking.pdf Unlocking the Postgres Lock Manager]<br />
* [http://michael.otacoo.com/wp-content/uploads/2011/09/20110916_pgopen_xc.pdf Postgres-XC]<br />
<br />
=== Mayfair Room ===<br />
* [http://wiki.postgresql.org/images/7/79/Accelerating_Local_Search_PostgreSQL_91.pdf Accelerating Local Search With PostgreSQL 9.1]<br />
* [http://bunsen.credativ.com/~jco/2011/plr-PostgresOpen-2011.pdf PL/R -- The Fast Path to Advanced Analytics]<br />
<br />
=== Michigan Room ===<br />
<br />
* [http://wiki.postgresql.org/images/7/7c/Development_Funding.pdf Get Your Preferred Feature Developed!]<br />
* [http://wiki.postgresql.org/images/0/0e/Center_of_your_dataverse.pdf PostgreSQL at the centre of your dataverse]<br />
* [http://htsql.org/talks/20110916.html Advanced Query Construction with HTSQL]<br />
* [http://wiki.postgresql.org/images/5/56/Database_sing.pdf We write the code that makes the database sing]<br />
* [http://wiki.postgresql.org/images/f/fa/Slony_Versus_Developers.pdf Slony Versus Developers]<br />
* [http://wiki.postgresql.org/images/7/7f/Adam-lowry-postgresopen2011.pdf Postgres at Urban Airship]<br />
<br />
[[Category:PostgreSQL Events]]</div>Jkshahhttps://wiki.postgresql.org/index.php?title=ProposalTemplate&diff=1469ProposalTemplate2008-06-11T18:57:59Z<p>Jkshah: ProposalTemplate on how to request a new feature</p>
<hr />
<div>== Proposal Template For a New Feature ==<br />
<br />
<br />
'''One-line Summary:''' Explain in one line regarding the feature so that most hackers can go through it and see if is interesting enough to read the rest of the proposal.<br />
<br />
<br />
'''Business Use-case:''' Explain the problem that you are trying to solve with the proposal.<br />
<br />
<br />
'''User impact with the change:''' What will change from the perspective of existing PostgreSQL user? (Maybe if it is a new feature they never had that syntax support or currently it gives some error, etc.)<br />
<br />
<br />
'''Implementation details:''' If you already have an idea on how it can be implemented.<br />
<br />
<br />
'''Estimated Development Time:''' Man-hours estimated for the proposal to be implemented. (If not sure, mention unknown)<br />
<br />
<br />
'''Opportunity Window Period:''' An end-date if by which if it is not completed, it will be useless to work on the implementation<br />
<br />
<br />
'''Budget Money:''' A range that the sponsor has in mind for the price of development of the proposal<br />
<br />
<br />
'''Contact Information:''' Contact information of the sponsor</div>Jkshahhttps://wiki.postgresql.org/index.php?title=How_to_sponsor_a_feature&diff=1468How to sponsor a feature2008-06-11T18:47:16Z<p>Jkshah: /* Propose the Feature */</p>
<hr />
<div>== How to sponsor a feature ==<br />
<br />
=== Propose the Feature ===<br />
<br />
* Write a detailed proposal. Here is a [[ProposalTemplate]]<br />
* Send it to the pgsql-hackers list.<br />
* Ask for feedback, and pay attention both to what comes back and to silence.<br />
* Lather, rinse, repeat.<br />
* Avoid sending such proposals during a [[CommitFest:Help|CommitFest]].<br />
<br />
=== Create A Specification ===<br />
<br />
You can:<br />
<br />
* Create this on your own.<br />
* Solicit community help.<br />
* Hire people to create the specification.<br />
* TODO: template for a specification<br />
<br />
=== Gather Consensus on the Specification ===<br />
<br />
* Send the specification back to pgsql-hackers and make sure you get affirmative agreement before proceeding further.<br />
<br />
=== Create Patches ===<br />
<br />
* Patches should be in the form of context-style diffs.<br />
* They should apply cleanly to CVS TIP.<br />
* Make sure you send patches early and often. Giant patch dumps are a great way to get your patch rejected and/or ignored.<br />
<br />
=== Submit Patches ===<br />
<br />
* Send patches to the pgsql-hackers list along with a reminder of what has gone before. Pointers into the archives are good if some time has elapsed.</div>Jkshahhttps://wiki.postgresql.org/index.php?title=Pgbench&diff=1429Pgbench2008-05-28T15:07:41Z<p>Jkshah: </p>
<hr />
<div>==pgbench==<br />
<br />
[http://developer.postgresql.org/pgdocs/postgres/pgbench.html pgbench] is a popular benchmarking tool used by many developers and hackers to do quick performance test with PostgreSQL on a system setup.<br />
<br />
On this page are recommendations from community on best ways to test with pgbench on a relatively bigger scale so that the frame of reference is consistent for the rest of the community reading into the results of pgbench<br />
<br />
==pgbench options==<br />
* -s = At minimum should be greater than the number of concurrent clients (-c) option and typically be bigger so as to be more meaningful<br />
* -c = Generally pgbench client becomes cpu limited somewhere between 150-250 concurrent users. Also using less than 2 will not generally show contentions in lock much<br />
* -t = Should be reasonably high. For example "HOT" benefit of limiting bloat in 8.3 is not clearly visible if the benchmark ran for short duration of time<br />
<br />
==Additional helpful information about parameters==<br />
* [http://www.westnet.com/~gsmith/content/postgresql/pgbench-scaling.htm Determining the right pgbench database size scale]<br />
<br />
==postgresql.conf parameters which need to be bumped up from defaults==<br />
* wal_buffers <br />
* shared_memory<br />
* max_connections<br />
<br />
<br />
==Setup Tuning==<br />
For a relatively bigger setup, it is recommended to run pgbench client (the program pgbench itself) from a separate system than the system being tested. This would mean that network between the two system needs to be optimized. Monitor pgbench client itself to see that it does not become CPU saturated process (either via top or prstat -am) <br />
<br />
==Disk Tuning==<br />
* Separate out Log (pg_xlog) to different file system)</div>Jkshahhttps://wiki.postgresql.org/index.php?title=Pgbench&diff=1426Pgbench2008-05-27T19:59:57Z<p>Jkshah: </p>
<hr />
<div>==pgbench==<br />
<br />
[[http://developer.postgresql.org/pgdocs/postgres/pgbench.html pgbench]] is a popular benchmarking tool used by many developers and hackers to do quick performance test with PostgreSQL on a system setup.<br />
<br />
On this page are recommendations from community on best ways to test with pgbench on a relatively bigger scale so that the frame of reference is consistent for the rest of the community reading into the results of pgbench<br />
<br />
==pgbench client==<br />
For a relatively bigger setup, it is recommended to run pgbench client (the program pgbench itself) from a separate system than the system being tested. This would mean that network between the two system needs to be optimized<br />
<br />
==pgbench options==<br />
* -s = At minimum should be greater than the number of concurrent clients (-c) option and typically be bigger so as to be more meaningful<br />
* -c = Generally pgbench client becomes cpu limited somewhere between 150-250 concurrent users. Also using less than 2 will not generally show contentions in lock much<br />
* -t = Should be reasonably high. For example "HOT" benefit of limiting bloat in 8.3 is not clearly visible if the benchmark ran for short duration of time<br />
<br />
<br />
==postgresql.conf parameters which need to be bumped up from defaults=<br />
* wal_buffers<br />
* shared_memory<br />
<br />
<br />
==Disk Tuning==<br />
* Separate out Log (pg_xlog) to different file system)</div>Jkshahhttps://wiki.postgresql.org/index.php?title=Pgbench&diff=1424Pgbench2008-05-27T19:53:08Z<p>Jkshah: New page: ==pgbench== http://developer.postgresql.org/pgdocs/postgres/pgbench.html pgbench is a popular benchmarking tool used by many developers and hackers to do quick performance test with P...</p>
<hr />
<div>==pgbench==<br />
<br />
[[http://developer.postgresql.org/pgdocs/postgres/pgbench.html pgbench]] is a popular benchmarking tool used by many developers and hackers to do quick performance test with PostgreSQL on a system setup.<br />
<br />
On this page are recommendations from community on best ways to test with pgbench on a relatively bigger scale so that the frame of reference is consistent for the rest of the community reading into the results of pgbench<br />
<br />
==pgbench client==<br />
For a relatively bigger setup, it is recommended to run pgbench client (the program pgbench itself) from a separate system than the system being tested. This would mean that network between the two system needs to be optimized<br />
<br />
==pgbench options==<br />
-s = At minimum should be greater than the number of concurrent clients (-c) option and typically be bigger so as to be more meaningful<br />
-c =<br />
<br />
<br />
==postgresql.conf parameters which need to be bumped up from defaults=<br />
wal_buffers<br />
shared_memory<br />
<br />
<br />
==Disk Tuning==</div>Jkshahhttps://wiki.postgresql.org/index.php?title=Performance_Optimization&diff=1423Performance Optimization2008-05-27T19:43:48Z<p>Jkshah: </p>
<hr />
<div>== General Setup and Optimization ==<br />
* [http://linuxfinances.info/info/quickstart.html QuickStart Guide to Tuning PostgreSQL] by Christopher Browne<br />
* [http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm 5-Minute Introduction to PostgreSQL Performance] by Greg Smith<br />
* [http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html Annotated postgresql.conf] by Josh Berkus and Shridhar Daithankar<br />
* [http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Performance Tuning] by Josh Berkus and Shridhar Daithankar<br />
* [http://www.zope.org/Members/pupq/pg_in_aggregates Replacing Slow Loops in PostgreSQL] by Joel Burton<br />
* [http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/ PostgreSQL Hardware Performance Tuning] by Bruce Momjian<br />
* [http://www.targeted.org/articles/databases/fragmentation.html The effects of data fragmentation in a mixed load database] by Dmitry Dvoinikov<br />
* [http://www.2ndQuadrant.com/download/Postgres_Performance_Update.pdf PostgreSQL Performance Features in 8.3] by Simon Riggs<br />
Performance courses are available from a number of companies. Check www.postgresql.org for details<br />
<br />
==Critical maintenance for performance==<br />
*[[Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT]] by Jim Nasby.<br />
*[[Planner Statistics]]<br />
*[[Using EXPLAIN]]<br />
*[[Logging Difficult Queries]]<br />
*[[Logging Checkpoints]]<br />
*[http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm Checkpoints and the Background Writer: PostgreSQL 8.3 Improvements and Migration] by Greg Smith<br />
<br />
==Disk and hardware setup==<br />
*[http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm Testing your disks for PostgreSQL] by Greg Smith<br />
*[http://www.commandprompt.com/blogs/joshua_drake/2008/04/is_that_performance_i_smell_ext2_vs_ext3_on_50_spindles_testing_for_postgresql/ Ext2 vs Ext3 on 50 spindles] by Joshua Drake<br />
*[[SCSI vs. IDE/SATA Disks]]<br />
*[[Direct Storage vs. SAN]]<br />
* [[Community Disk Tuning Guide]] (work in progress)<br />
<br />
==Additional helpful manual sections==<br />
*[http://www.postgresql.org/docs/current/interactive/populate.html Populating a Database]: covers bulk-loading<br />
<br />
==Benchmark Workloads== <br />
* [[pgbench]] <br />
* [[DBT-5]] (TPC-E) <br />
* [[EAStress]] <br />
<br />
==Performance and Scaling Reports==<br />
* [http://blogs.sun.com/jkshah/resource/pgcon_problems.pdf PgCon 2008 Benchmark and Scaling Update] by Jignesh Shah, et all (Sun)</div>Jkshah