[GENERAL] how do I do dump and restore without bugging with constraint?
Hi all, I am dumping both schema and data from old database to new one. The new database schema is somehow contain slightly different schema then the old one. When I do restore it shown alot errors related with constraints. How can I dump and to restore from old to new without dealing with constraint and just forces data dump to where it suppose to belong?
Re: [GENERAL] how do I do dump and restore without bugging with constraint?
Ok, I am just trying to find the proper way to back and restore database that contain restriction. The other way to ask. If I have two 99% schema similar databases. The old one might contain few columns that does not exist in the new one and contain constrains that the new DB does not have. In that case, what's standard to do this? Since they are 99% similar in schema, can I do "data dump" only ? will that work? If owner for database between Old and new DB are different, do I must add that owner in by create new role? appreciated, Steven On Tue, Feb 23, 2010 at 1:09 PM, John R Pierce wrote: > Net Tree Inc. wrote: > >> Hi all, >> >> I am dumping both schema and data from old database to new one. The new >> database schema is somehow contain slightly different schema then the old >> one. When I do restore it shown alot errors related with constraints. How >> can I dump and to restore from old to new without dealing with constraint >> and just forces data dump to where it suppose to belong? >> >> >> >> > > if you're dumping the schema and the data, you should be restoring into an > empty database, not into an existing database. > > > -- --- Steven Huang
[GENERAL] Re: [ADMIN] how do I do dump and restore without bugging with constraint?
Why am I keep getting error with version issues I am trying to restore a backup file from a 8.4 postgresql server to a 8.3 postgresql server. [postg...@localhost ~]$ pg_restore -C -d postgres -i isamdb.backup pg_restore.bin: [archiver] unsupported version (1.11) in file header On Tue, Feb 23, 2010 at 1:02 PM, Kevin Grittner wrote: > "Net Tree Inc." wrote: > > > When I do restore it shown alot errors related with constraints. > > How can I dump and to restore from old to new without dealing with > > constraint and just forces data dump to where it suppose to belong? > > What version are you dumping from and to? Exactly what errors are > you getting? (Copy and paste samples, please.) > > You might want to review this page: > > http://wiki.postgresql.org/wiki/Guide_to_reporting_problems > > -Kevin > > > -- --- Steven Huang
[GENERAL] pg_dump: aborting because of version mismatch
>>I am keep getting error of mismatch of pg_dump version. how should one
dealing with different version of pg_dump normally?
C:\Program Files\pgAdmin III\1.8\pg_dump.exe -h 192.168.222.129 -p 5433 -U
postgres -F c -b -v -f "C:\Documents and
Settings\steven\Desktop\template.backup" template_postgis
pg_dump: server version: 8.3.9; pg_dump version: 8.3.0
pg_dump: aborting because of version mismatch (Use the -i option to proceed
anyway.)
pg_dump: *** aborted because of error
Process returned exit code 1.
>>If I use the '-i' option it still give the following error
[postg...@localhost ~]$ pg_dump -h 172.16.1.246 -p 5432 -U postgres -Fc -i
postgres > postgres
Password:
pg_dump.bin: server version: 8.4.0; pg_dump.bin version: 8.3.9
pg_dump.bin: proceeding despite version mismatch
pg_dump.bin: SQL command failed
pg_dump.bin: Error message from server: ERROR: column "reltriggers" does
not exist
LINE 1: ...oles WHERE oid = relowner) as rolname, relchecks, reltrigger...
^
pg_dump.bin: The command was: SELECT c.tableoid, c.oid, relname, relacl,
relkind, relnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
relowner) as rolname, relchecks, reltriggers, relhasindex, relhasrules,
relhasoids, d.refobjid as owning_tab, d.refobjsubid as owning_col, (SELECT
spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS
reltablespace, array_to_string(c.reloptions, ', ') as reloptions from
pg_class c left join pg_depend d on (c.relkind = 'S' and d.classid =
c.tableoid and d.objid = c.oid and d.objsubid = 0 and d.refclassid =
c.tableoid and d.deptype = 'a') where relkind in ('r', 'S', 'v', 'c') order
by c.oid
Re: [GENERAL] pg_dump: aborting because of version mismatch
Thanks..
This is what I am confused about. I installed a ver. 8.4 postgresql, why
it's pg_dump is 8.3.9?? For first one, how could this possibly having
problem using pg_dump that comes with the server install??
the first one I backup using pgAdmin III ver. 1.8.4 on a ver 8.3.9
postgreSQL server
the second one I backup using command line on a ver 8.4 postgreSQL server
Is this something that can solved by upgrade or reinstall ver. 8.4 of
pg_dump?
Is that its only downward compatible but not upward? which mean I can do
restore on 8.4 from backup created by ver 8.3 pg_dump, but probably won't
work the other way around? Definitely impossible? then what is '-i' use
for.?
Steven
On Tue, Feb 23, 2010 at 5:55 PM, Magnus Hagander wrote:
> 2010/2/23 Net Tree Inc. :
> >>>I am keep getting error of mismatch of pg_dump version. how should one
> dealing with different version of pg_dump normally?
> > C:\Program Files\pgAdmin III\1.8\pg_dump.exe -h 192.168.222.129 -p 5433
> -U postgres -F c -b -v -f "C:\Documents and
> Settings\steven\Desktop\template.backup" template_postgis
> > pg_dump: server version: 8.3.9; pg_dump version: 8.3.0
> > pg_dump: aborting because of version mismatch (Use the -i option to
> proceed anyway.)
> > pg_dump: *** aborted because of error
> > Process returned exit code 1.
> >>>If I use the '-i' option it still give the following error
> >
> > [postg...@localhost ~]$ pg_dump -h 172.16.1.246 -p 5432 -U postgres -Fc
> -i postgres > postgres
> > Password:
> > pg_dump.bin: server version: 8.4.0; pg_dump.bin version: 8.3.9
> > pg_dump.bin: proceeding despite version mismatch
> > pg_dump.bin: SQL command failed
> > pg_dump.bin: Error message from server: ERROR: column "reltriggers" does
> not exist
> > LINE 1: ...oles WHERE oid = relowner) as rolname, relchecks,
> reltrigger...
> > ^
> > pg_dump.bin: The command was: SELECT c.tableoid, c.oid, relname, relacl,
> relkind, relnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
> relowner) as rolname, relchecks, reltriggers, relhasindex, relhasrules,
> relhasoids, d.refobjid as owning_tab, d.refobjsubid as owning_col, (SELECT
> spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS
> reltablespace, array_to_string(c.reloptions, ', ') as reloptions from
> pg_class c left join pg_depend d on (c.relkind = 'S' and d.classid =
> c.tableoid and d.objid = c.oid and d.objsubid = 0 and d.refclassid =
> c.tableoid and d.deptype = 'a') where relkind in ('r', 'S', 'v', 'c') order
> by c.oid
>
>
> You're doing two different things here. In the first one it's pg_dump
> 8.3.0 against server 8.3.9. This should, I think, work if you use -i.
> It's not recommended, but it should work.
>
> In the second one, it's pg_dump 8.3.9 against server 8.4.0. This is
> simply not supported. You need to use pg_dump 8.4 for server 8.4.
>
>
> --
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/
>
--
---
Steven Huang
Re: [GENERAL] Re: [ADMIN] how do I do dump and restore without bugging with constraint?
yeah that's what I means to do. How do I use 8.4 pg_restore? the DB server I am trying to restore is using 8.3. Do you mean do pg_restore on the same machine that I did pg_dump?? I am thinking of doing that too, but I am not sure how to do the command. Is this correct? pg_restore -h (my target machine IP) -U postgres -d (target db) mydb.dump Steven On Tue, Feb 23, 2010 at 10:31 PM, Richard Huxton wrote: > On 23/02/10 09:17, Net Tree Inc. wrote: > >> Why am I keep getting error with version issues I am trying to restore >> a >> backup file from a 8.4 postgresql server to a 8.3 postgresql server. >> > > Well, an 8.4 dump isn't always going to be compatible with an 8.3 server, > is it? If there weren't differences, the version number wouldn't have > changed. > > > [postg...@localhost ~]$ pg_restore -C -d postgres -i isamdb.backup >> pg_restore.bin: [archiver] unsupported version (1.11) in file header >> > > Try using the 8.4 pg_restore (but keep the "-i" to tell it to ignore the > version checks). You may still see errors, but there is a good chance it > will work. > > You're also restoring into the "postgres" database here - is that what you > meant to do? > > -- > Richard Huxton > Archonet Ltd > -- --- Steven Huang
[GENERAL] Re: [ADMIN] how do I do dump and restore without bugging with constraint?
Thanks. Is it by doing these steps I can avoid constrain restriction? for step 3, how should I modify the schema? and which schema? the target DB's schema that I am trying to dump the schema and data in? But this is the problem, I am not sure whats different between the two schema's, there are just too many tables to check. What's your tips? Steven On Tue, Feb 23, 2010 at 7:03 PM, Iñigo Martinez Lasala < imarti...@vectorsf.com> wrote: > Using pg_dump from your new host (that is, newer version) > > 1- Dump schema. > pg_dump -h server_source -U username -s -Fp -f schema.sql database > > 2- Dump data only. > pg_dump -h server_source -U username -a -Fc -f data.dmp database > > 3- Modify schema. > > 4- Restore schema in new host. > psql -U username -d database -h server_destination -f modified_schema.sql > > 5- Restore data disabling triggers > pg_restore -h server_destination -U username -d database --disable-triggers > -a data.dmp > > > > -Original Message- > *From*: Net Tree Inc. > > > > *To*: pgsql-ad...@postgresql.org, pgsql-general@postgresql.org > *Subject*: [ADMIN] how do I do dump and restore without bugging with > constraint? > *Date*: Tue, 23 Feb 2010 12:50:27 +0800 > > Hi all, > > I am dumping both schema and data from old database to new one. The new > database schema is somehow contain slightly different schema then the old > one. When I do restore it shown alot errors related with constraints. How > can I dump and to restore from old to new without dealing with constraint > and just forces data dump to where it suppose to belong? > > > > > > > > > > -- --- Steven Huang
[GENERAL] Re: [ADMIN] how do I do dump and restore without bugging with constraint?
Quote: "However if you have to modify table definitions, you will probably not be able to import data in that new schema and it will be necesary to look for a new strategy." For schema, are we talking about attribute columns (structure of table) and "table definitions" referraled you talking about things like "data types"?If error came out during restore are related with table definitions then it's probably no help?I see something related with Deferrable and Initially deferrable that seems like something could avoid constraints when dumping and restore, but it has to modify the table or re-create all of them to have such option (maybe is what you referraled "table definitions"). Is it what it can be use for to avoid during dumping and restoring? DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the *SET CONSTRAINTS*<http://www.postgresql.org/docs/8.3/static/sql-set-constraints.html>command). NOT DEFERRABLE is the default. Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. INITIALLY IMMEDIATE INITIALLY DEFERRED If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the *SET CONSTRAINTS*<http://www.postgresql.org/docs/8.3/static/sql-set-constraints.html>command. On Wed, Feb 24, 2010 at 1:49 AM, Iñigo Martinez Lasala < imarti...@vectorsf.com> wrote: > To avoid contraints you have to use –disable-triggers flag during > restore. That is done in step 5. > > > > In order to modify schema you have to locate what is failing. So, first > restore old schema without modifications in your new database, watch for > errors and fix them. > > For example: > > psql -U username -d database -h server_destination -f schema.sql > 2>import_error.log > > > > You’ll find all import errors in import_error.log. Since you are only going > to launch an schema definition, It will only take a few seconds, so test all > you need. > > After locating errors, open schema.sql with your favorite editor and fix > them. There are minor changes between postgresql versions and you’ll > probably have no problems in order to fix them, or ask here if you have many > problems. > > Once fixed, you will have your new modified_schema.sql and you can proceed > with steps 4 and 5. > > > > Obviously, if errors are related to indexes, functions, broken > dependencies, etc without touching table definitions, everything will go > well. However if you have to modify table definitions, you will probably not > be able to import data in that new schema and it will be necesary to look > for a new strategy. > > > > *De:* Net Tree Inc. [mailto:nettree...@gmail.com] > *Enviado el:* martes, 23 de febrero de 2010 17:36 > *Para:* Iñigo Martinez Lasala > *CC:* pgsql-ad...@postgresql.org; pgsql-general@postgresql.org > *Asunto:* Re: [ADMIN] how do I do dump and restore without bugging with > constraint? > > > > Thanks. Is it by doing these steps I can avoid constrain restriction? for > step 3, how should I modify the schema? and which schema? the target DB's > schema that I am trying to dump the schema and data in? But this is the > problem, I am not sure whats different between the two schema's, there are > just too many tables to check. What's your tips? > > Steven > > On Tue, Feb 23, 2010 at 7:03 PM, Iñigo Martinez Lasala < > imarti...@vectorsf.com> wrote: > > Using pg_dump from your new host (that is, newer version) > > 1- Dump schema. > pg_dump -h server_source -U username -s -Fp -f schema.sql database > > 2- Dump data only. > pg_dump -h server_source -U username -a -Fc -f data.dmp database > > 3- Modify schema. > > 4- Restore schema in new host. > psql -U username -d database -h server_destination -f modified_schema.sql > > 5- Restore data disabling triggers > pg_restore -h server_destination -U username -d database --disable-triggers > -a data.dmp > > > > > -Original Message- > *From*: Net Tree Inc. > > > > *To*: pgsql-ad...@postgresql.org, pgsql-general@postgresql.org > *Subject*: [ADMIN] how do I do dump and restore without bugging with > constraint? > *Date*: Tue, 23 Feb 2010 12:50:27 +0800 > > Hi all, > &g
Re: [GENERAL] how do I do dump and restore without bugging with constraint?
I see something related with Deferrable and Initially deferrable that seems like something could avoid constraints when dumping and restore, but it has to modify the table or re-create all of them to have such option (maybe is what you referraled "table definitions"). Is it what it can be use for to avoid during dumping and restoring? DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the *SET CONSTRAINTS*<http://www.postgresql.org/docs/8.3/static/sql-set-constraints.html>command). NOT DEFERRABLE is the default. Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. INITIALLY IMMEDIATE INITIALLY DEFERRED If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the *SET CONSTRAINTS*<http://www.postgresql.org/docs/8.3/static/sql-set-constraints.html>command. On Tue, Feb 23, 2010 at 12:50 PM, Net Tree Inc. wrote: > Hi all, > > I am dumping both schema and data from old database to new one. The new > database schema is somehow contain slightly different schema then the old > one. When I do restore it shown alot errors related with constraints. How > can I dump and to restore from old to new without dealing with constraint > and just forces data dump to where it suppose to belong? > > > > > -- --- Steven Huang
[GENERAL] Frequently unable connecting to db "server doesn't listen"
If this e-mail address is not intend use for asking questions using e-mail suscription, please ignore it. I could not find any official PostgreSQL support forum for me to post ask questions. Appreciated if you could you refer me a few good PostgreSQL support forum for me to ask questions. Problem: The problem I am having has happened a few times within in a week. I am repeatly not able to connect to the db and having "server doesn't listen" message without touch anything, it just happen. First time it happen after I restarting my computer that has not been restart for almost a month. After restarting and trying to connect to DB and it failed, also I notice my computer can not be restart nor shutdown under the normal way by click on "start menu" and "shutdown or restart". I can only shut it down use the hard way by pressing the power button, I don't know why, but its another story. Therefore for DB, I only can fix it by uninstall and reinstall PostgreSQL. But it happening repeatly. Any one experience it? or to guide me to where I can find help?
