RDS Crosstab – Fixing ‘Permission Denied for Language C’ Error

amazon-rdsawspivotpostgresqlpostgresql-9.4

We're currently using PostgreSQL 9.2 in our production database, we want to migrate to a new RDS instance, using PostgreSQL 9.4. However, we need to have permission to create a function, but only superuser can create functions on RDS and my user can't be granted as superuser.

CREATE FUNCTION crosstab50(text, OUT rownumber integer, OUT c1 text,...) 
    RETURNS SETOF record 
    LANGUAGE c 
    STABLE STRICT 
    AS '$libdir/tablefunc', 'crosstab';

But we're getting this error during our restore operation:

ERROR:  permission denied for language c

Best Answer

RDS doesn't let you install arbitrary C functions. It's a restricted environment.

crosstab is part of the tablefunc extension. You may be able to CREATE EXTENSION tablefunc if it's on the whitelist but you won't be able to add new variants, even if the underlying C function is already loaded and approved.

Related Solutions

Postgresql – “Zero” downtime and minimal space requirements for upgrade to PostgreSQL 9.2

The two constraints that you have specified:

Very low downtime
Limited disk space use

conflict rather seriously.

You have (wisely, IMO) excluded the option of using pg_upgrade with a custom rebuilt Pg, which is the only option I see that'd satisfy both those constraints.

I suspect you'll have to drop the disk space constraint. The only way out of this that I see is to configure Slony-I to replicate from your 8.3 database to a new 9.2 instance deployed either on new storage on the same machine, or on a separate machine. Allow Slony-I to bring the replica up to date, then shut the old server down, disable replication and cut over to the new server. "New server" here can be a new 9.2 cluster running on a different port on the existing server hardware, it doesn't have to be a new physical or virtual machine.

At this point you can use streaming replication - or Slony-I again - to replicate the data back to a 9.2 instance on the original server and fail back to it. The temporary server used for migration can be retired, or (preferably, if it's real hardware) configured to operate as a streaming replication slave and WAL archiving repository for PITR and failover purposes.

Slony-I is a bit of an experience to work with and places some constraints on how you can use the DB (mainly: All DDL must go through Slony, not direct DDL commands), so I recommend taking a dump of the 8.3 server, restoring it to a test workstation, and testing the proposed replication setup there before attempting it in production. You will need to test your application on 9.2 anyway, so this is a good chance to do both. Test it on 8.3-with-slony, and on 9.2.

This might be a good opportunity to see professional support from people who have experience dealing with these issues.

Postgresql – Restoring SQL_ASCII dumps to a UTF8-encoded database

I suspect your database content may be in iso8859 or cp1252. If it were ascii, you would not run into problems importing it. You may be able to determine the coding by opening your dump with python. The following python3 tries encodings until it succeeds. It can be used to determine the file encoding.

for enc in ('cp1252', 'utf8'):
    print('Encoding', enc)
    try:
        file = open(fileName, 'r', encoding=enc)
        return file.read()
    except Exception as e:
        print(e)
        return None

Python also can also be used to access the database and audit the data. It could also be used to transcode the data to UTF-8 while copying it if necessary.

Best Answer

Related Solutions

Postgresql – “Zero” downtime and minimal space requirements for upgrade to PostgreSQL 9.2

Postgresql – Restoring SQL_ASCII dumps to a UTF8-encoded database

Related Question