6) Get column names for a table
<span class="k">select</span> <span class="k">column_name</span> <span class="k">from</span> <span class="n">information_schema</span><span class="p">.</span><span class="n">columns</span> <span class="k">where</span> <span class="k">table_name</span><span class="o">=</span><span class="s1">'items'</span>
Proprietary licences both frustrating and pushing move to PostgreSQL
According to trade magazine Computer Sweden, RAÄ wanted to continue using an older Oracle Database 9.3 system for a limited time, before migrating to the PostgreSQL open source software.
RAÄ had a licence to allow it to run Oracle Database 9.3, but Oracle insisted that the agency also renew its support contract for the system. According to the supplier, a customer must have the same level of support for all copies of Oracle Database used in the organisation — and RAÄ had recently renewed a support contract for a more recent version of Oracle Database. So according to Oracle, the agency had two choices. It could have the existing licence — bought and paid for — nullified and stop using the database software now. Alternatively it could renew the support contract for a software version that soon would no longer be supported by Oracle anyway.
Bordering on a scamIn a follow-up comment, Lars Danielsson, a reporter at IDG Enterprise Sweden, called these practices “bordering on a scam”.
Sharding a multi-tenant app with Postgres
Co-locating data within the same physical instance avoids sending data over the network during joins. This can result in much faster operations. With Citus there are a number of ways to move your data around so you can join and query it in a flexible manner, but for this class of multi-tenant SaaS apps it’s simple if you can ensure data ends up on the shard.
Postgres Backup with Wal-e
The following are the steps I took to setup Wal-e 0.6.2 on Ubuntu 12.04.2 LTS and Postgres 9.1.9. After following the installation instructions, every minute Wal-e will make incremental backups to Amazon S3.
Installation
$ sudo apt-get install libevent-dev python-all-dev daemontools lzop pv postgresql-client $ sudo pip install wal-e $ umask u=rwx,g=rx,o= $ mkdir -p /etc/wal-e.d/env $ echo "secret-key-content" > /etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY $ echo "access-key" > /etc/wal-e.d/env/AWS_ACCESS_KEY_ID $ echo 's3://some-bucket/directory/or/whatever' > /etc/wal-e.d/env/WALE_S3_PREFIX $ sudo chown -R root:postgres /etc/wal-e.dAdded the following to the end of the file, /etc/postgresql/9.1/main/postgresql.conf:
wal_level = archive archive_mode = on archive_command = 'envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-push %p' archive_timeout = 60Restart postgres:
$ sudo service postgresql restart