Advice for geographically dispersed multi master
-BEGIN PGP SIGNED MESSAGE- Hash: SHA512 Hi, /* Disclaimer: I am primarily a systems administrator with some database and DBA history. Although these days I would normally leave this kind of thing to a seasoned DBA this is for my personal use, and the use of a non- profit organisation that I am doing some volunteer support work for. */ So I am looking to move my current setup of 3 separate, individual, postgres 11 servers into a multi master cluster. I have done this with OpenLDAP, which is used for various purposes, but want to extend this to cover my DB installs. I have read the various replication and clustering documentation for postgresql 11, and it looks like what I want is "Synchronous Multimaster Replication". The organisation I am doing this for does not have the money to throw at a commercial solution like BRD, but from my reading it looks like PG v12 may have a method to make such a solution easier to achieve with stock PG. The three servers I am using are all in different DC's (2 VPS and one bare metal install). I would like to have all my servers containing all the PG databases on each server for various reasons (to supplement backups, and make recovery faster/easier. To allow applications on different servers access to various databases locally (rather than transmitting all SQL requests over network). The list goes on, but I don't want to bore people more than I already have.). There is also the possibility that a subset of these databases may be replicated to a fourth, or even fifth location depending on needs that arise. So: 1) Should I just wait for PG v12 to come out of RC status and try to set it up then? 2) Is there are way to reconfigure my current servers to replicate the databases to the other servers? 3) Is there a better way to do this full stop? Any help/advice is greatly welcomed, and appreciated. - -- Nikolai Lusan -BEGIN PGP SIGNATURE- iQIzBAEBCgAdFiEEVfd4GW6z4nsBxdLo4ZaDRV2VL6QFAl2VucEACgkQ4ZaDRV2V L6TCvhAAhBGF46Rl+1vFLK8oafG1kiURbRTtuZVHQO26Eb0pi4eceClhmWXx39aU 8wl8XVjiOcZyQD8/yjX9jI0jT13p2C9eqcLQeDGUXc3vUYC/iuF8o3pQV3qwZTRL +FyRL4rjgtjfDYh4nVNd+3dowwdpsE8G1K7NYL2uZvS3CYj4YkdwoM24hBHBe2fU PdLYOOqYKDJwQMVtbxexgd+Ig0+y31IVLK7c/2VavnKvRjN5B4U+BfiBkaofyTyZ KNhyUxxL65czH6ThDHcX4r3HdHPHy9LffEwMcndn7w/cFtjNcj+XxRcCS/WiYR6u Y4GkCutoEZW4KdX8/GyCLIoH7PgToD3PV1t5iiaXQm+SfDKopdVDjBNaVCKMuUL4 weQ667MoJRbV1MA1ASQhx28RV8UgcpWjBXsyXimpQ610sLNVqDxbhadsYJyaWjoG y95y/3ZAXMGx7xjgfu67M52wzEN5wHgzBMA99gF7UbIHnXkw3Xe1v0C1XrORRkuT vyIx0MyYxyZiJ2g2zxG1YuhW1AorWNWGrricJNeK9lLdcvPx7svTTZshGjC4GmWZ vGVqmQcxJDY19AtbwsevKsp1FO4dGqh9LTQsiOrX47WB7BckUzBgsz0uU7HEFuqh /GuEz131l5ddLBoEhdu2TCL3zWudnXj5NKeklyw9BBXPTslkH1s= =7Rs2 -END PGP SIGNATURE-
The best way to solve a problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA512 Greeting database brains, Although I started my career with a short lived role as a database administrator, and over the years have created a few small databases of my own, I normally do most of my database things on DB's that have been created by other people - and even then rarely mess with the internals. However I have a small problem that I can't figure the best way to solve. I am a member of a small sporting association that I am doing some technical stuff for, part of which is designing and implementing a DB for membership and scoring records. The logic required in application to do the scoring system is under control, as is the few tables required for tracking memberships. The problem I have is tables for the scoring. Each year there are multiple competitions run, over multiple grades. The first team round robin competition of each year is used to determine which teams play in which grades for the main season, and to handicap players based on a win/loss percentage. I need to keep data around for historical purposes (I know this means ever increasing storage issues), but some things (like team home locations) can be kept in a table that has the data changed at the beginning of each competition. There are also two different types of singles competitions run each year. All players are members of the association, so putting all in one DB makes sense to me, since I can then easily reference players by their membership numbers (or the sequence number that identifies their entry in the main membership table). I am not sure if I should be setting up some kind of template table for the various competition types and then using it to create either new tables, or new schemas, for each competition (something named like __). I need to be able to track player statistics through each round they play (the number of rounds in each competition varies depending on the number of teams/individuals playing) for a variety of reasons, and for the team competitions I need to be able to track the results of each round, and the number of games played/won/lost in each round. I would like a solution where I can easily remove historical data should the committee decide that they only want to keep X years of records for these competitions. My initial thought was to put the tables for each competition into a separate schema, but I'm not sure if this is the right way to go. In fact I'm not entirely sure I am approaching this whole thing in the right way. Does anyone have any ideas that could help me create an elegant solution to this in a Postgres DB? - -- Nikolai Lusan -BEGIN PGP SIGNATURE- iQIzBAEBCgAdFiEEVfd4GW6z4nsBxdLo4ZaDRV2VL6QFAl46DhUACgkQ4ZaDRV2V L6S2yQ/7BneZvFaVAHhaM0Yb9Ttr9W73iBau2nZryfgo0yYNL8zdJlC89gMqese5 oLSJA32kuAo/v7G2RA7O+4UYI4/Jou2cHZNUQh17u+B88FQ/vxE96w2Fge8q+h/t hF5C8DObEnuNwfJGzi1VpIpHlyQicD9C2nD0skRLgBvLjQXHiG8SOW7+SBd5uo/r XrsBgr/fuDQM8hEm/FtHNbspUwXMF0Yrwn5so2EqvwbHhS/By0I0TC+2/77Vawkx 008hKadAXc746tc56HH3nyAd7cUhWxNmLVRtMUc5feylIJEEYcMpf7Ybyo5pgv3T 9IO2+dCl4zL3wgolkjMjt5ofHlIkA0fPKVFrsaaROFNgtwiKx1KvSrmA5qBIzjjW 36mLH4kVjVOEhu35F4J9kGLgXbkfgqTQGRn/AdxUN5RagqJSVRkv+dxNyRmjIIHe qDqmJg5G9s6K2Vt+/TU0RRAGIXbYy0dj+ZLX1DSpsFuqr90935IH1OlVhO2sD2z9 gX6MtgQ8zVfcn+Omj5rvcNUkIpw9hP+lYMTfBU8CW1IOHhxTaif9HOW7sqF/IrOQ 3gg2FYCsqGeGgTdrT8n4+4EFImNThNhd9oR6aZPvZ0b7Mos+X7/Mwm4gCTpZQCXU xhBpiVOErIG+DcVhjVnahLpapew+JboOWr0kwb0BIlKYxtgFrAw= =aIeT -END PGP SIGNATURE-
Re: The best way to solve a problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA512 Hey, On Tue, 2020-02-04 at 18:14 -0700, David G. Johnston wrote: > On Tue, Feb 4, 2020 at 5:36 PM Nikolai Lusan wrote: > > > I > > am a member of a small sporting association that I am doing some > > technical > > stuff for, part of which is designing and implementing a DB for > > membership > > and scoring records. > > > > [...] > The rest of that planning seems like a significant case of premature > optimization. PostgreSQL has built-in partitioning now but even that > seems > like an unnecessary addition to your data model at this point. Trying to > create it manually using schemas is something you probably should just be > discarded. This is good to know, I have been using PGModeler to build and verify the DB. I was putting things in schemas as a form of logical separation for the next person who comes along to admin this solution (or migrate data from it to another solution) sometime down the track. > A matches table with adequate category fields to classify the type of > game > being played and its outcome seems like it should be sufficient. The > business logic you describe is something that queries on that table can > solve. Reading the detail a bit more you probably want a "match" table > and > a "match_result" table so you can pre-load matches that you know are > going > to happen and then insert a corresponding record with the outcome once > the > match is complete. That said, NULL can be useful is this limited > situation > as well. A bit more disclosure, this is for an Eight Ball (pool) association. The team based matches are a 16 frame round robin match (nominally 4 players per team, but there is the possibility of up to 6 players used by a team in any given match). The 2 forms of singles are round robin (potentially with multiple pools of players and seeding of players). Having match data for a specific division is essential, as are the individual player stats. > Your needs regarding historical data are not fully clear but you can > generally consider either discarding old information or copying current > data into the match table so that you archive the known values at the > time > the match took place. Again, I wouldn't worry about the duplication onto > a > read-only table or the space that it will take - your dataset size > doesn't > seem like it will be large enough to matter. With about 300 players per year and 26 team rounds + 7 singles tournaments the possibility of it growing is there, but I agree not really large enough to matter. Having data around for previous years tournaments is of use if a player stops playing for a while and then needs to be handicapped again, also for records on the website. This is why I was thinking of putting things in one table per tournament per year (possibly two tables for keeping track of teams and fixture dates). I found a stackexchange post from a person looking to do something similar in MySQL that a total of 3 tables - but I think I want something more extensive than this. > You can always make changes later as new requirements are added or > constraints such as size become more important. Yeah, but I am doing some forward planning right now. Some features done now won't be used for a while (there are a couple of older committee members resistant to change, and some others who want to come into the 21st century.). - -- Nikolai Lusan -BEGIN PGP SIGNATURE- iQIzBAEBCgAdFiEEVfd4GW6z4nsBxdLo4ZaDRV2VL6QFAl46bCoACgkQ4ZaDRV2V L6QkBA//Tx/lgiIC8qbXqFPQ2UjeOdNwoO3ArknHCy8Mxwtiy7rtX6sfNxr206jr wfFpAENuoBapssrYbVyyEvGzV5fIxwPYZAb71PxA7MtyW5m9RZKgiQlKIXam5gXc NVjoT35KGsjlavD5skq8aqQNblJBZ7fFeWh8KpolliVrahh9umO6JEFuq/NK79PY WF73qKGhy24ulHQsfxQFEvw3BWwWN7l9Xk4zFJbzFrni2XZNQxOg76k67RQJX8rC 1LxIJcCEHo1mWMpBAul64705OEZmjPH71f27yBKM2gDSgpDbdHlP2QxGAooYjZ8y CmTZ5fmU3e87T4mHIFBkPNH2jhPZ50C4c3l90TEjdGarmVSvmXoGaIW7K7B07P+8 1WMU/a03UViEHZbD4idXSmmmr6oFdqdI55mg+72B2EWtjbu+2Cp58x7gD20KT2nC ukDqd4+kZSEtRCgyICenCwI1Zt3nlMS7jPFSZpbvtzCLbFj0XmM2Hft02IIvo4E1 3J6wKmq+yQ/u4uT7g4iXTVUFR5WSNLQ2m3DbIjBjDYfQC/hsCcwk6MNC58Gp+j7U gJU4i3BntdSf8pKpGjoYnl1N7qqQLlFEQE0oC0iCCkCcyd5/bx9efEiJOcexjJdn x82QEWhFj9VlI5MDFH/Qdk0SrVl3hSwb8I3moON60iqSbDnovtE= =vHp0 -END PGP SIGNATURE-
Feature Requests
-BEGIN PGP SIGNED MESSAGE- Hash: SHA512 Hi, I was wondering where I can see open feature requests. One I would like to see is multi-master replication ... I did find a 2016 request that was marked as "planned", but to the best of my knowledge it hasn't made production yet. - -- Nikolai Lusan -BEGIN PGP SIGNATURE- iQIzBAEBCgAdFiEEVfd4GW6z4nsBxdLo4ZaDRV2VL6QFAl+VRLYACgkQ4ZaDRV2V L6SLPRAArH5dTHFvTFiO9C5SGyguz+ez4OOt7fA16pT+WZIz694mko6rMxUz+hlb RnNV6z5huC5EMcjFL6RHBg/Tx4hKIdidARLvTGycTML66iIpTTngdLueQX8YnLZ1 9/hLIv14Ms7vA1VAhjromhcZeUQD18BBpmoLy4Kk+drIQHs8viCfeaX5PKUOggT6 tUOTZGf0fMqwQHNhmtT22qa3FCHWYI2zReSZ1hscKgwxjTnOhArqhq5dW1+IdrBE fsZ8qKUBtO25CN7dbgT6E1znrFPieMELhh9b9AJ5IsXMNUqtPoXvVFyW9ZrUoTHB GeVbHLEtEFhbyLoVyfgfgCbWcorrew6/Dv24Xq/GZheVfuyRZTcIWIfiKaFFI0vc xyFz1CxHs1ZRWvNaC8YJPLPIESUEuWtJWyFxf1TK0rxxXVf11eg/p54eZUjbNlAA vUCDSMMnTVcInepcvAj88PuRldcy0x/4dgr92oRdBzmHSo1IHGrcEAKxd8+GK/a8 Ox5DgoZCvjGOyjQD/P859ScxB+WuIeF/+oujSDRHBF+YG/pDH73RhuH2/IfbvhqU AEdvRnuJyb93fuQ/j58mP2bKg/7aU/ECvghelzmT8pj+H1xYKEVxRugy1SOWth7A /r2/GveuyIBSD1v/026wbkSK+cwc0bq1uTjHJqCJtrUlni6Jm60= =pz31 -END PGP SIGNATURE-
