Postgresql – Postgres sql dump import error

postgresqlpostgresql-9.2

I'm facing a issue while importing the dump in my system, i'm getting the error as

" Error: syntax error at or near "" LOCK TABLESDB_NAME` WRITE;"

and exiting with "out of memory"

We had taken back-up from Suse linux and trying to import in Cent OS 6.3.

Any suggestion to fix this issue.

Thanks in Advance,

Best Answer

LOCK TABLES dname WRITE is not a valid PostgreSQL statement. It's a MySQL statement. You can't just take a MySQL dump and restore it to Postgres. You'll need to convert it either manually or find some kind of converter script.

Related Solutions

Postgresql – Dropped database still shows but causes errors PostgreSQL

I would guess you had open connections / transactions while you dropped the db initially and the DROP is stalled somehow.

You can get more info on databases with statistics views. For instance:

SELECT * FROM pg_stat_database WHERE datname = 'mixmobi_master';

In case you find open backend processes, you might want to shut them down like this:

SELECT pg_terminate_backend(procpid)
FROM   pg_stat_activity
WHERE  datname = 'mixmobi_master';

More about that in the manual here.

Find a safe method to drop a database under load at this related answer:

Force drop db while others may be connected

Postgresql – Recover PostgreSQL database from WAL errors on startup

UPDATE: it looks like this is a bug in the Debian/Ubuntu packaging of PostgreSQL, where the init scripts - extremely unsafely - kill -9 the postmaster and remove postmaster.pid. See this post on pgsql-general.

See:

Personally, I've gone and edited my init scripts to get rid of this rather hairy and dangerous code.

The original answer

Please go back in the logs to before the restart and see if you can find any errors. WAL corruption absolutely should not happen, so if it has it's important to look into why. If you can upload a copy of the whole log to a pastebin or something that'd be really handy.

The only time where WAL corruption is an accepted possibility with PostgreSQL is if you are running with fsync=off set in PostgreSQL.conf and your system crashes or unexpectedly loses power. If that's not the cause, it'd be really good to look into what happened.

Please do not use pg_resetxlog without some idea why your xlogs are damaged. If the transaction logs become damaged something is badly wrong and you need to find out what. If you band-aid it now, you might be bitten by it later when you care about the data.

The transaction logs exist for a reason and just removing them can leave your tables and indexes in an inconsistent, damaged state. After a pg_resetxlog it's a very good idea to pg_dumpall, drop your cluster, re-initdb, and reload the DB. As I said, though, this should not happen and you should look back in the logs for clues about what could've happened.

Best Answer

Related Solutions

Postgresql – Dropped database still shows but causes errors PostgreSQL

Postgresql – Recover PostgreSQL database from WAL errors on startup

The original answer

Now read the comments

Related Question