[GENERAL] Streaming replication and WAL archives
TLDR: We want to be able to use streaming replication, WAL archiving, and have the ability to restore from a backup made before a failover using the WAL archive. Setup: We use postgresql 9.1 with streaming replication between a pair of nodes. The nodes are identical, and can fail over and fail back between master/slave pairs. We have separate reliable storage where we store periodic base backups and WAL archives. On each node, archive_command and restore_command write and read from this shared location. Currently, WAL archives are written from the current master to a shared location. On failover, the new master will write WAL files with the same name as already exist in the archive. Currently we allow these to overwrite the old files. (See more below) When a failed node comes back, if it is able to resume streaming from the new master, it does. Otherwise it does an rsync backup from the new master and eventually gets back into sync by replaying archived WAL files. WAL archiving by new master: When the new master comes up, it wants to archive some of the WAL files it has. These WAL files have the same name as files that have already been archived by the old master, but different contents. Newer WAL files in the new timeline cannot be archived until the old ones are archived. For this reason we allow the old files to be overwritten. This works consistently for *failover*, and is effectively the same thing as having each node write archives to a separate location, and have each only restore from the others' archives. Note that some of the contents of the WAL files on the old slave were never archived, and do need to be archived. The slave has transactions that were part of the open WAL file on the master that the master had not archived, yet, but that the slave received via streaming replication. So the end of the old timeline can only come from the old slave/new master. Restore: We also want to be able to restore from the base backups. The problem we are encountering is that a server restored from a base backup fails to continue to restore once it hits a WAL file that was overwritten. I.E. that was written by the new master after a failover. Related discussions: http://www.postgresql.org/message-id/CADKfymHjBa9=edv1z8qh8g9o44ia2wncrmnuowqj90djl+y...@mail.gmail.com http://www.postgresql.org/message-id/CAHGQGwHVYqbX=a+zo+avfbvhlgoypo9g_qdkbabexgxbvgd...@mail.gmail.com Questions: - Is there a single series of WAL files that will be the full timeline for the old timeline? I.E. if we had every WAL file from each node, could we put them together in a way that would allow us to replay them from a backup made before the failover? - Is there a better way to handle any of this? - Is there something we're missing? Thanks, -Alan Bryant -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming replication and WAL archives
On Mon, 25 Nov 2013, Tree wrote: TLDR: We want to be able to use streaming replication, WAL archiving, and have the ability to restore from a backup made before a failover using the WAL archive. (cutting rest of long description) So, is it possible to use a "long-term WAL archive area" (as the docs refer to this) using streaming replication, and accounting for failover? Thanks, -Alan Bryant -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 < [email protected]> 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*: [email protected], [email protected] > *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 < [email protected]> 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:[email protected]] > *Enviado el:* martes, 23 de febrero de 2010 17:36 > *Para:* Iñigo Martinez Lasala > *CC:* [email protected]; [email protected] > *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 < > [email protected]> 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*: [email protected], [email protected] > *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?
