Thread: Help! Database restored with disabled triggers
I have database backup schema+data in text (non-compressed) format. Backup is created using "pg_dump -i -h ... -U ... -f dump.sql". I run it with "psql <dump.sql" but after restore all triggers are disabled! I can't use this text dump with pg_restore because it only accept archived dumps. And I am not sure that using pg_restore will solve disabled triggers problem. I need to have the backup in text format so I can open and edit it. There was a recipe earlier in this mailing list that involves writing a function that will enable all triggers one-by-one. But I want to do it a proper way, without such "hacking". What would be the solution for me? Thanks.
On Tuesday 22 September 2009 7:28:03 pm Joe Kramer wrote: > I have database backup schema+data in text (non-compressed) format. > Backup is created using "pg_dump -i -h ... -U ... -f dump.sql". > I run it with "psql <dump.sql" but after restore all triggers are disabled! > > I can't use this text dump with pg_restore because it only accept > archived dumps. And I am not sure that using pg_restore will solve > disabled triggers problem. > I need to have the backup in text format so I can open and edit it. > > There was a recipe earlier in this mailing list that involves writing > a function that will enable all triggers one-by-one. But I want to do > it a proper way, without such "hacking". > > What would be the solution for me? > > Thanks. What version of Postgres are you dumping from, restoring to? Which version of pg_dump are you using? -- Adrian Klaver aklaver@comcast.net
Joe Kramer <cckramer@gmail.com> writes:
> I have database backup schema+data in text (non-compressed) format.
> Backup is created using "pg_dump -i -h ... -U ... -f dump.sql".
> I run it with "psql <dump.sql" but after restore all triggers are disabled!
You sure they weren't disabled in the source database? AFAICS pg_dump
just duplicates the trigger state it sees in the source.
regards, tom lane
On Thu, Sep 24, 2009 at 12:02 AM, Adrian Klaver <aklaver@comcast.net> wrote: > On Tuesday 22 September 2009 7:28:03 pm Joe Kramer wrote: >> I have database backup schema+data in text (non-compressed) format. >> Backup is created using "pg_dump -i -h ... -U ... -f dump.sql". >> I run it with "psql <dump.sql" but after restore all triggers are disabled! >> >> I can't use this text dump with pg_restore because it only accept >> archived dumps. And I am not sure that using pg_restore will solve >> disabled triggers problem. >> I need to have the backup in text format so I can open and edit it. >> >> There was a recipe earlier in this mailing list that involves writing >> a function that will enable all triggers one-by-one. But I want to do >> it a proper way, without such "hacking". >> >> What would be the solution for me? >> >> Thanks. > > What version of Postgres are you dumping from, restoring to? Which version of > pg_dump are you using? > > -- I am using client 8.1.9 to dump from server 8.3.0 (unable to use client 8.3.x) Importing to server 8.3.7.
On Thu, Sep 24, 2009 at 12:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Joe Kramer <cckramer@gmail.com> writes: >> I have database backup schema+data in text (non-compressed) format. >> Backup is created using "pg_dump -i -h ... -U ... -f dump.sql". >> I run it with "psql <dump.sql" but after restore all triggers are disabled! > > You sure they weren't disabled in the source database? AFAICS pg_dump > just duplicates the trigger state it sees in the source. > > regards, tom lane > Yes, I'm absolutely sure they are not disabled. And in the SQL dump file there are no commands that would disable them. It simply goes on to creating triggers, but in the end they are all disabled. Regards.
Joe Kramer <cckramer@gmail.com> writes:
> On Thu, Sep 24, 2009 at 12:02 AM, Adrian Klaver <aklaver@comcast.net> wrote:
>> What version of Postgres are you dumping from, restoring to? Which version of
> I am using client 8.1.9 to dump from server 8.3.0 (unable to use client 8.3.x)
> Importing to server 8.3.7.
You mean you are dumping from an 8.3 server with an 8.1 pg_dump?
That is pretty much guaranteed not to work; I am surprised that the
only symptom you notice is bad trigger state. Why do you feel
you can't use an up-to-date pg_dump?
regards, tom lane
Joe Kramer <cckramer@gmail.com> writes:
> On Thu, Sep 24, 2009 at 12:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You sure they weren't disabled in the source database?
> Yes, I'm absolutely sure they are not disabled. And in the SQL dump
> file there are no commands that would disable them.
Better take another look for "ALTER TABLE foo DISABLE TRIGGER bar"
commands. Given the information that this is a pre-8.3 pg_dump,
that's exactly the behavior I'd expect, because it's not going to
understand the values it finds in pg_trigger.tgenabled in an 8.3
server.
regards, tom lane
On Thu, Sep 24, 2009 at 1:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Joe Kramer <cckramer@gmail.com> writes: >> On Thu, Sep 24, 2009 at 12:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> You sure they weren't disabled in the source database? > >> Yes, I'm absolutely sure they are not disabled. And in the SQL dump >> file there are no commands that would disable them. > > Better take another look for "ALTER TABLE foo DISABLE TRIGGER bar" > commands. Given the information that this is a pre-8.3 pg_dump, > that's exactly the behavior I'd expect, because it's not going to > understand the values it finds in pg_trigger.tgenabled in an 8.3 > server. > Thanks, I found DISABLE TRIGGER commands and deleted them, but wish I could find a way to make pg_dump not to add them!
----- "Joe Kramer" <cckramer@gmail.com> wrote: > On Thu, Sep 24, 2009 at 1:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Joe Kramer <cckramer@gmail.com> writes: > >> On Thu, Sep 24, 2009 at 12:53 AM, Tom Lane <tgl@sss.pgh.pa.us> > wrote: > >>> You sure they weren't disabled in the source database? > > > >> Yes, I'm absolutely sure they are not disabled. And in the SQL > dump > >> file there are no commands that would disable them. > > > > Better take another look for "ALTER TABLE foo DISABLE TRIGGER bar" > > commands. Given the information that this is a pre-8.3 pg_dump, > > that's exactly the behavior I'd expect, because it's not going to > > understand the values it finds in pg_trigger.tgenabled in an 8.3 > > server. > > > > Thanks, I found DISABLE TRIGGER commands and deleted them, > but wish I could find a way to make pg_dump not to add them! > You are going to have to use the 8.3 pg_dump :) Adrian Klaver aklaver@comcast.net
On Wed, Sep 23, 2009 at 9:12 AM, Joe Kramer <cckramer@gmail.com> wrote: > I am using client 8.1.9 to dump from server 8.3.0 (unable to use client 8.3.x) > Importing to server 8.3.7. That won't work