[GENERAL] Constraint allowing value up to 2 times but no more than 2 times
Greeetings. I'm trying to come up with a way to allow one column to have the same value up to two times, but no more than two times. I attempted adding a constraint to check the count of the value in the field - count (trsqqq) <=2 but aggregate functions are not allowed in constraints. Is there another way to do this? Any help would be greatly appreciated. Thanks in advance. Dara *--Dara J. Olson Unglaube* Aquatic Invasive Species Coordinator, Spatial Database Manager Great Lakes Indian Fish & Wildlife Commission P.O. Box 9, 72682 Maple Street Odanah, WI 54861 (715) 682-6619 ext.2129 d...@glifwc.org maps.glifwc.org
Re: [GENERAL] Rebuild streaming replication
Thank you - it worked beautifully. Dara On Thu, Mar 12, 2015 at 4:28 PM, Chander Ganesan wrote: > Hi Dara, > > A few things: > > 1. Replication is a binary process - that means that you can never set it > up with using any of the pg_dump type tools (i.e., pg_dumpall/pg_dump can > never be used.) > 2. PostgreSQL provides a special tool to make this process super-simple: > http://www.postgresql.org/docs/9.1/static/app-pgbasebackup.html > > So the steps should be as follows: > > 1. Don't touch the master (you really should never have to touch the > master's config/run state once you have replication setup, except for > performance tuning, etc.) > 2. Install PostgreSQL on the slave (if it already exists there, stop the > slave server.) > 3. On the slave server run the pg_basebackup tool (note: you may need to > delete the contents of the data directory on the slave first): > > pg_basebackup -D -c -P -h -U > > > 4. If your config files are in your data directory of the master, they'll > all be replicated over as well - if they are not then you'll need to copy > them over by hand. > > Hope that helps! > > Chander > > > > On 3/12/15 5:05 PM, Dara Unglaube wrote: > > We had two servers set up with streaming replication. I believe when we > did a full vacuum analyze to the database on the master, it caused the > slave to not be able to catch up (wal_keep_segments = 32, but still not > enough and had archive off). I am trying to re-set up the slave to > streaming replication. Below are the steps I took (attempted a couple times > now). Does anyone have any idea what I am doing wrong? The service is > unable to start after I copy and paste the files over. > >- Uninstalled and re-installed postgres on slave and master (v9.1.10) > > >- Stopped slave postgres service and added files and settings for >streaming replication to the slave (postgresql.cof, recovery.conf - which >all worked in the past set up) >- Loaded the master with a pg_dumpall backup file. >- Stopped the master postgres service. > - Copied the data directory from master to slave minus pg_xlog >folder, postgresql.conf and postgresql.pid files using rsync > - Attempted to start postgres on the slave and it is unable to start > - Postgres log: > - CDT LOG: entering standby mode > - CDT FATAL: the database is starting up (multiple times) > - CDT FATAL: could not connect to the primary server... (assume > this is ok because master has not been started yet) > - Event Viewer, Administrative Events > - Timed out waiting for server start up > > Any help, thoughts, comments, tips, etc would be greatly appreciated. > Thanks! > Dara > > > > -- > Chander Ganesan > Open Technology Group, Inc. > 11010 Lake Grove Blvd Ste. 100-307 > Morrisville, NC 27560 > 919-463-0999/877-258-8987http://www.otg-nc.com > >
[GENERAL] Rebuild streaming replication
We had two servers set up with streaming replication. I believe when we did a full vacuum analyze to the database on the master, it caused the slave to not be able to catch up (wal_keep_segments = 32, but still not enough and had archive off). I am trying to re-set up the slave to streaming replication. Below are the steps I took (attempted a couple times now). Does anyone have any idea what I am doing wrong? The service is unable to start after I copy and paste the files over. - Uninstalled and re-installed postgres on slave and master (v9.1.10) - Stopped slave postgres service and added files and settings for streaming replication to the slave (postgresql.cof, recovery.conf - which all worked in the past set up) - Loaded the master with a pg_dumpall backup file. - Stopped the master postgres service. - Copied the data directory from master to slave minus pg_xlog folder, postgresql.conf and postgresql.pid files using rsync - Attempted to start postgres on the slave and it is unable to start - Postgres log: - CDT LOG: entering standby mode - CDT FATAL: the database is starting up (multiple times) - CDT FATAL: could not connect to the primary server... (assume this is ok because master has not been started yet) - Event Viewer, Administrative Events - Timed out waiting for server start up Any help, thoughts, comments, tips, etc would be greatly appreciated. Thanks! Dara
Re: [GENERAL] Streaming Replication - changing IP addresses
Thank so very for the info, John! This will help a lot to have it set up locally. I have it set up across the local network and double checked with pg_stat_replication and it was using the local IP. But now I am not sure about the "archive_command" command on the master. We had it set up to archive onto the slave through the archive_command using SSH & rsync because we were going through the public IP. Below is the command line. archive_command = 'rsync -az -e "\"/cygdrive/c/cygwin64/bin/ssh.exe\"" "/cygdrive/d/PostgreSQL/9.1/data/%p" "postgres@0.0.0.0:/cygdrive/d/PostgreSQL/9.1/data/wals/%f" ' Is this neccessary? What is the benefit of having the archive on? Could I map a network drive from the slave to the master and set the archive_command to that mapped drive? Or what would be the best approach for this? Thanks so very much! Dara On Mon, Dec 8, 2014 at 2:11 PM, John R Pierce wrote: > On 12/8/2014 11:56 AM, Dara Unglaube wrote: > > We have streaming replication set up on two servers that are on our local > network using their external/public IP addresses. We are switching internet > providers and need to change the external/public IP addresses of both > servers. I'm not sure how to go about this correctly. > >1. Our local network IP addresses will not be changing, is there any >reason we could/should not use our local network IP addresses in the >streaming replication process? > 2. What is the best route to make these changes? Is it as simple as >turning postgres off on both servers, changing the IP settings (master - >postgres.conf and hba.conf and slave - recovery.conf files to the local >network IPs) and starting the service up again? Can anyone point me to an >example? > > Any tips, suggestions, help you could provide would be greatly appreciated. > > Thanks in advance for your time! > > > if these two servers are both local, there's absolutely no reason to use > public IP space for this, they should be using their local addresses, > faster, lower latency, otherwise each round trip packet is going through 4 > layers of NAT (out, in, then out, in again to return). > > make sure the master allows the slave replication account to connect via > its local IP (this would be in pg_hba.conf, and a 'reload'), then change > the address the slave is using to connect to the master, and restart the > slave, and it all should be good. > > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > > -- *Dara J. Olson Unglaube* Aquatic Invasive Species Coordinator Great Lakes Indian Fish & Wildlife Commission P.O. Box 9, 72682 Maple Street Odanah, WI 54861 (715) 682-6619 ext.2129 d...@glifwc.org maps.glifwc.org
[GENERAL] Streaming Replication - changing IP addresses
Greetings. We have streaming replication set up on two servers that are on our local network using their external/public IP addresses. We are switching internet providers and need to change the external/public IP addresses of both servers. I'm not sure how to go about this correctly. 1. Our local network IP addresses will not be changing, is there any reason we could/should not use our local network IP addresses in the streaming replication process? 2. What is the best route to make these changes? Is it as simple as turning postgres off on both servers, changing the IP settings (master - postgres.conf and hba.conf and slave - recovery.conf files to the local network IPs) and starting the service up again? Can anyone point me to an example? Any tips, suggestions, help you could provide would be greatly appreciated. Thanks in advance for your time! Dara
