http://blog.gmane.org/gmane.comp.db.postgresql.slony1.general hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10722 <pre>The slony.info website will be taken offline for maintenance on: Wednesday, May 23 starting at 7:30pm * The website - www.slony.info * The slony bugzilla instance * All slony mailing lists will be offline, this is expected to last a few hours. Thanks </pre> Steve Singer 2012-05-22T21:23:59 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10721 <pre>In the event of a schema change we will be taking down the master node and one slave node, rebuilding replication on them then doing the other slave nodes one at a time. Ex we have 3 slave nodes. This is how I envision it going. - Update schema on master &amp; slave 1 - Create replication set on master &amp; slave 1 - Update schema on slave 2 - Add slave 2 to replication set - Update schema on slave 3 - Add slave 3 to replication set From what I'm reading, I would not have to add the paths for slave 2 &amp; 3 until I am ready to add them to the set, is this correct? Shaun McCloud, MCDST | Associate Software Developer Geo-Comm Inc. | www.geo-comm.com -----Original Message----- From: Christopher Browne [mailto:cbbrowne-1UFIP0iZ1TeZuzBka8ofvg&lt; at &gt;public.gmane.org] Sent: Tuesday, May 22, 2012 12:53 To: Shaun McCloud Cc: slony1-general-8kkgcvHRObyz5F2/bZa4Fw&lt; at &gt;public.gmane.org Subject: Re: [Slony1-general] Run pgAdmin II Slony-I Creation scripts outside of pgAdmin III? On Tue, May 22, 2012 at 1:40 PM, Shaun McCloud &lt;smccloud-G2GxFBbTuLNWk0Htik3J/w&lt; at &gt;public.gmane.org&gt; wrote: Everything that can be configured can certainly be configured using Slonik; its use shouldn't restrict you in any additional ways. I don't imagine you can actually get "100% uptime," but that's a broader issue not particularly relevant to this. There are some actions that are likely to lead to some locking of database objects (see the Slony documentation; each Slonik command has a section describing locking implications) that would effectively lower uptime from 100%. </pre> Shaun McCloud 2012-05-22T17:54:38 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10720 <pre> Everything that can be configured can certainly be configured using Slonik; its use shouldn't restrict you in any additional ways. I don't imagine you can actually get "100% uptime," but that's a broader issue not particularly relevant to this. There are some actions that are likely to lead to some locking of database objects (see the Slony documentation; each Slonik command has a section describing locking implications) that would effectively lower uptime from 100%. </pre> Christopher Browne 2012-05-22T17:52:54 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10719 <pre>Ok, I will do more research using that method. I was planning on allowing a user to run the app on the Master node and each slave node to allow then to add one slave at a time (due to the fact that we must maintain 100% uptime in the event of a schema change). That shouldn't be an issue when using Slonik directly, correct? Shaun McCloud, MCDST | Associate Software Developer Geo-Comm Inc. | www.geo-comm.com -----Original Message----- From: Christopher Browne [mailto:cbbrowne-1UFIP0iZ1TeZuzBka8ofvg&lt; at &gt;public.gmane.org] Sent: Tuesday, May 22, 2012 12:12 To: Shaun McCloud Cc: slony1-general-8kkgcvHRObyz5F2/bZa4Fw&lt; at &gt;public.gmane.org Subject: Re: [Slony1-general] Run pgAdmin II Slony-I Creation scripts outside of pgAdmin III? On Tue, May 22, 2012 at 11:36 AM, Shaun McCloud &lt;smccloud-G2GxFBbTuLNWk0Htik3J/w&lt; at &gt;public.gmane.org&gt; wrote: The last discussion I had (if memory serves, with Dave Page, almost exactly a year ago, at PGcon... If my memory isn't entirely correct, then it's still Dave Page, still PGcon, but it would be *TWO* years ago...) indicated that pgAdmin III hasn't been tracking Slony functionality for a *long* time now. What pgAdmin III *had* been doing was to run some of the Slony stored procedures, effectively duplicating parts of what is implemented in Slonik (&lt;http://git.postgresql.org/gitweb/?p=slony1-engine.git;a=tree;f=src/slonik;h=819d809475fdd45b3d2d58107ae39d7eb7c77a5d;hb=HEAD&gt;). Now, what they last duplicated was most likely code from Slony 1.2. (Hence looking like &lt;http://git.postgresql.org/gitweb/?p=slony1-engine.git;a=tree;f=src/slonik;h=92406ed729c6418b779134fd177f54aaae038174;hb=refs/heads/REL_1_2_STABLE&gt;.) There have been rather a lot of changes since version 1.2, and functionality of slonik varies, in ways that sometimes matter, from version to version to version, and the pgAdmin III devs stopped tracking things way back. The recommendation in these "modern days" is that cluster management should be done using Slonik scripts, and for Slony 2.1 and later, that's really rather crucial, as slonik has functionality that wouldn't be terribly appropriate to even attempt to integrate into pgAdmin III. </pre> Shaun McCloud 2012-05-22T17:40:05 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10718 <pre> The last discussion I had (if memory serves, with Dave Page, almost exactly a year ago, at PGcon... If my memory isn't entirely correct, then it's still Dave Page, still PGcon, but it would be *TWO* years ago...) indicated that pgAdmin III hasn't been tracking Slony functionality for a *long* time now. What pgAdmin III *had* been doing was to run some of the Slony stored procedures, effectively duplicating parts of what is implemented in Slonik (&lt;http://git.postgresql.org/gitweb/?p=slony1-engine.git;a=tree;f=src/slonik;h=819d809475fdd45b3d2d58107ae39d7eb7c77a5d;hb=HEAD&gt;). Now, what they last duplicated was most likely code from Slony 1.2. (Hence looking like &lt;http://git.postgresql.org/gitweb/?p=slony1-engine.git;a=tree;f=src/slonik;h=92406ed729c6418b779134fd177f54aaae038174;hb=refs/heads/REL_1_2_STABLE&gt;.) There have been rather a lot of changes since version 1.2, and functionality of slonik varies, in ways that sometimes matter, from version to version to version, and the pgAdmin III devs stopped tracking things way back. The recommendation in these "modern days" is that cluster management should be done using Slonik scripts, and for Slony 2.1 and later, that's really rather crucial, as slonik has functionality that wouldn't be terribly appropriate to even attempt to integrate into pgAdmin III. </pre> Christopher Browne 2012-05-22T17:12:14 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10717 <pre>Is it possible/feasible to use the Slony-I Creation scripts designed for use in pgAdmin III outside of pgAdmin III? I need to write a utility to make it easier for our installers to setup Slony-I and I'd like to use the official scripts to do it but I'm unsure how to go about it right now. Shaun McCloud, MCDST | Associate Software Developer Geo-Comm Inc. | www.geo-comm.com&lt;http://www.geo-comm.com/&gt; 601 W. Saint Germain St., St. Cloud, MN, 56301 Office: 320.240.0040 or 888.436.2666 Fax: 320.240.2389 _______________________________________________ Slony1-general mailing list Slony1-general-8kkgcvHRObyz5F2/bZa4Fw&lt; at &gt;public.gmane.org http://lists.slony.info/mailman/listinfo/slony1-general </pre> Shaun McCloud 2012-05-22T15:36:13 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10716 <pre> On May 16, 2012, at 7:46 AM, Stuart Bishop wrote: Probably should consider a writing a systemd file for Fedora as well. </pre> Jeff Frost 2012-05-19T00:29:04 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10715 <pre> Is all this actually on host harv, or is this on a different system? Jan </pre> Jan Wieck 2012-05-16T16:33:38 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10714 <pre> Its maintained upstream in Debian by Peter Eisentraut, and from there gets pulled into Ubuntu. I don't see a 2.1 package either - last upload seems to be slony1-2 2.0.7. http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-postgresql-public seems to be the forum for the Debian packaging. </pre> Stuart Bishop 2012-05-16T14:46:42 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10713 <pre> I am open to creating a ubuntu directory and including this script there (just like we have a redhat and suse directory). Who is maintaining the slony ubuntu packaging these days? I haven't found a 2.1.x package. </pre> Steve Singer 2012-05-16T13:34:07 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10712 <pre>I wrote an upstart script for starting slony on ubuntu 10.04+. If there's any interest in including it in the slony distro, you can find it here: https://github.com/pgexperts/upstart-scripts/blob/master/etc/init/slony.conf </pre> Jeff Frost 2012-05-15T21:52:44 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10711 <pre>Not sure what I'm missing but I get this error when I try to run the slony config. I'm running this at postgres. I ran the config earlier this week and we had to stop slony. Try to run it today but fails $ ./slony-conf.sh &lt;stdin&gt;:21: PGRES_FATAL_ERROR load '$libdir/slony1_funcs'; - ERROR: could not access file "$libdir/slony1_funcs": No such file or directory &lt;stdin&gt;:21: Error: the extension for the Slony-I C functions cannot be loaded in database 'dbname=canvas_db host=harv user=postgres' It all looks correct but can't find slony1_funcs when running conf $ find /opt/postgres -name slony1_funcs.so /opt/postgres/9.0.3-pgdg/lib/slony1_funcs.so $ pg_config --libdir /opt/postgres/9.0.3-pgdg/lib $ pg_config --pkglibdir /opt/postgres/9.0.3-pgdg/lib $ ll /opt/postgres/9.0.3-pgdg/lib/slony1_funcs.so -rwxr-xr-x 1 postgres postgres 39K Apr 9 14:28 /opt/postgres/9.0.3-pgdg/lib/slony1_funcs.so 0___ Wolfgang Schwurack c/ /'_ SA/DBA - UEN (*) \(*) 801-587-9444 wolf-OzwJb8Xg5oY&lt; at &gt;public.gmane.org </pre> Wolf Schwurack 2012-05-12T01:42:38 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10710 <pre> The AIX man page (open) says --- O_EXCL If the O_EXCL and O_CREAT flags are set, the open is unsuccessful if the file exists. Note: The O_EXCL flag is not fully supported for Network File Systems (NFS). The NFS protocol does not guarantee the designed function of the O_EXCL flag. ----------- Can we think of another way of ensuring the file doesn't exist? </pre> Steve Singer 2012-05-07T14:16:24 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10709 <pre> Your patch does in fact compile on Win32. I don't see any problems with applying this patch. </pre> Steve Singer 2012-05-08T18:57:57 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10708 <pre> When two remote_worker threads try to get a sequence number at the same time they will hit this. We are depending on the postgresql aborting one of the transactions , as you see above, to ensure two threads don't get the same id number for the filename. So the logs you pasted don't indicate a problem (or the source of your issue). Does anyone (Chris, Jan?) remember why we just didn't use a sequence on the master for this purpose? Apr 29 20:13:31 myhost.example.com </pre> Steve Singer 2012-05-08T19:02:49 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10707 <pre> The Linux documentation for O_EXCL states: ------ O_EXCL is only supported on NFS when using NFSv3 or later on kernel 2.6 or later. In environments where NFS O_EXCL support is not provided, programs that rely on it for performing locking tasks will contain a race condition. Portable programs that want to perform atomic file locking using a lockfile, and need to avoid reliance on NFS support for O_EXCL, can create a unique file on the same file system (e.g., incorporating hostname and PID), and use link(2) to make a link to the lockfile. If link(2) returns 0, the lock is successful. Otherwise, use stat(2) on the unique file to check if its link count has increased to 2, in which case the lock is also successful. ---------- I can see people wanting to put their log shipping spool directory on an NFS share. Have many NAS appliances out there still only support NFS v2? How does O_EXCL behave against a CIFS share? How does this call work on Win32? </pre> Steve Singer 2012-05-07T14:09:18 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10706 <pre> The node that has the -a option on its slon is the node number that shows up in the file name. Ie slony1_log_3_XXXXXXXXX.sql is a log file generated by slon # 3. slon 3 has a remote_worker for each of the remote nodes. These remote_worker threads run concurrently. Each one will generate a tracking file for SYNC events from its remote_worker. The archive sequence numbers are supposed to be assigned on the node the slon is for (node 3 is this case). So two remote_worker threads inside of slon 3 SHOULDN'T ever get the same archive counter number (we should be serializing on an update of the archive_counter table), thus they shouldn't be writing to the same file. One theory is that some of the "shouldnt's" are actually happening (for reasons we haven't determined) </pre> Steve Singer 2012-05-07T19:22:58 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10705 <pre> Sorry, I was thinking you were proposing this patch for commit on the 2.0, 2.1, 2.2 branches not just for Richard. I wouldn't be opposed to a patch that doesn't make things worse on nfs/cifs systems (if it compiles on win32). I think we both agree that the real problem is what is causing two files to be opened at the same time, and we don't yet have a handle why that is the case. </pre> Steve Singer 2012-05-07T18:58:38 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10704 <pre> Some more details about that: The log file I have seen contains an OK but empty archive log at the beginning. All the usual content but just no data. Then follow ASCII NUL bytes up to exactly 32K, followed by some log data starting in the middle of a statement but not properly terminated by the final commit and vacuum statements. The way I can create a file with a similar pattern is as follows: proc1: FILE *fp = fopen("file", "w"); proc1: fprintf(fp, "data from proc1\n"); // continue doing that until // over 32K written proc2: FILE *fp = fopen("file", "w"); proc2: fprintf(fp, "data from proc2\n"); proc2: fclose(fp); proc1: fprintf(fp, "data from proc1\n"); proc1: fclose(fp); What happens here is that both processes use fprintf(), like slon, and fprintf uses an internal buffer of 32K. When proc1 has written over 32K, the buffer had been flushed and the underlying file descriptor's lseek position is at 32K. Now comes proc2 and opens the file also for writing. The underlying file descriptor is created with O_CREAT only, which means that the file gets truncated. proc2 writes its stuff and closes the file. When proc1 finally closes the file it flushes its second buffer. Since the file was not opened in append mode, the truncate that happened in the meantime does not affect the lseek position in its file descriptor, so it writes that buffer at the 32K position and the OS fills in the void with NUL bytes. I have attached a small patch against 2.0 that changes the opening of the temporary archive file to use O_CREAT|O_EXCL and then fdopen(3). This would cause proc2 above to fail with an error because the file already exists. This will tell you in the slon logs which slon is actually doing this. Jan </pre> Jan Wieck 2012-05-05T14:14:16 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10703 <pre> We have several clusters writing to a directory, but there's a separate directory for each cluster. For example: /home/log_ship /home/log_ship/cluster1/new_logfiles /home/log_ship/cluster2/new_logfiles /home/log_ship/cluster3/new_logfiles ...etc... We don't have two daemons writing to the same directory. 2. The mechanism you used for copying the .sql files could have caused I'm fairly certain this is not the case. The files that I sent you were directly from the origin machine, not from the destination machine. Our scheme is like this: Node1 is origin Node2 is subscriber, with -a mode, writing files to /home/log_ship/cluster1/new_logfiles Cronjob moves files from /home/log_ship/cluster1/new_logfiles to /home/log_ship/cluster1/log_staging (we filter out the *.sql.tmp files so that we can let them finish writing before we move them) RemoteNode makes rsync connection to Node2 and copies the files from Node2/home/log_ship/cluster1/log_staging to its local directory Log files are replayed As I look at the files you sent me, I only see differences between the third (Node X, Event XXXXXX) and seventh (archiveTracking_offline(xxx,'xxxx-xx-xx xx:xx:xx')) lines. I noticed that Node number can vary per file, but only one daemon has the -a option enabled. Not sure why the node number changes--shouldn't it always correspond to the node number of the daemon with the -a option turned on? Aside from that, I tried poking around the sl_* tables that I had dumped, but didn't really find anything. One thing is certain, though--a given DML statement shows up in sl_log_x only once, even though it shows up several times in the various logship files that are generated. I can't seem to find the corresponding sl_event row, so I'm not sure if there might be anything in that direction, in terms of duplicated events. --Richard _______________________________________________ Slony1-general mailing list Slony1-general-8kkgcvHRObyz5F2/bZa4Fw&lt; at &gt;public.gmane.org http://lists.slony.info/mailman/listinfo/slony1-general </pre> Richard Yen 2012-05-04T21:46:26 http://permalink.gmane.org/gmane.comp.db.postgresql.slony1.general/10702 <pre> Removing the node information is definitely the wrong way. If you want to stop the replicas from talking to each other, remove the paths with the slonik command "DROP PATH". That way they will exchange their information via the central origin and everything will work. Jan </pre> Jan Wieck 2012-05-04T12:10:09 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.postgresql.slony1.general