Postgresql – Postgres 9.3 changes the standard_conforming_strings value

pg-dumppostgresqlschema

I'm migrating a legacy application's database from Postgres 8.1 to Postgres 9.3

In order to avoid compatibility issues, I'd like to keep the standard_conforming_strings parameter to it's default value in 8.1: off.

So, I create the databases using following in the schema:

SET standard_conforming_strings = off;

But a pg_dump of the database reveals that Postgres has changed the value to on.

SET standard_conforming_strings = on;

How can I prevent this?

Best Answer

How exactly did you set standard_conforming_strings?

If you did that in your session, it only applies to that session. For a permanent setting change your postgresql.conf and reload.

Also, a pg_dump cannot reveal anything. The setting in the dump only applies to the dump. To see the current setting of your database, run

SHOW standard_conforming_strings;

Or:

SELECT * FROM pg_settings WHERE name = 'standard_conforming_strings';

Here you also see where the current value is from and whether it's the default etc.

Generally, It's a very bad idea to try and keep standard_conforming_strings off. This was changed for a good reason. It's going to bite you sooner or later. Update to the current (superior) standard.

Related Solutions

Postgresql – Postgres, MVCC, and Locking

to 1) Any other session will read the data modified by your transaction as it was before your "BEGIN" statement as long your transaction didn't commit. As soon your transaction committed, it will read the new value of the counter. The point being that others don't have to wait and will always see a consistent data base.

to 2), 3) Why don't you try it with "ACCESS EXCLUSIVE"? (see http://www.postgresql.org/docs/current/static/explicit-locking.html)

EDIT: If you dislike locking the whole table with an "ACCESS EXCLUSIVE" lock, you could also use an "Advisory Lock" (see section 13.3.4 in the link above).

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 – Postgres, MVCC, and Locking

Postgresql – Restoring SQL_ASCII dumps to a UTF8-encoded database

Related Question