Re: [GENERAL] 9.3 debian install setup failure
On 03/21/2014 05:29 PM, john.tiger wrote: thks for quick comments - see below: On 03/21/2014 06:00 PM, Adrian Klaver wrote: On 03/21/2014 04:47 PM, john.tiger wrote: 9.3 install on debian jessie 64amd sudo -u postgres psql => worked alter user postgres with password 'password' => alter role su - postgress enter password => authentication failure So did you really type postgress (note extra s) or is this just an email error? email error hba.conf edited to allow "trust" to local - shouldn't this be set up as standard default ? This is a packager decision, in other words something Debian did. I didn't mean it was pre-set to trust - actually pre set to "peer" - I set it to "trust" to try to fix failure - no luck - it did not work but do think default should be set to "md5" or "trust" but based on mail list msgs maybe md5 is better than trust still authentication failure (after rebooting) now sudo -u postgres psql => could not connect - is server running - shouldn't install set up server to be booted on start up ? You would think. Are you sure it is not running? In other words have you done a ps ax and looked to see if it is running. It could also be that it running a listening on a different port. It would be helpful if you showed the actual full error messages. The fragment you show above (if I am assuming right) usually asks if the server is running and listening on port sorry - trying to install on another machine not running email - I am not following. Is this the same install we have been diagnosing and you are saying it is on different machine then your email client, or are trying a new seperate install on a different machine? "connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432" I am guessing there is more to this error message. Have you checked in /var/run/postgresql/ to see if the socket is being set up there? ps aux => shows it running btw - trying to install this on chromebook running crouton - wonder if this is causing EDIT from @rodrigo hmm, user postgres vs os postgres - okay understand what you mean but how is this fixed ? or what is proper procedure ? -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.3 debian install setup failure
On 03/21/2014 06:38 PM, john.tiger wrote: On 03/21/2014 06:43 PM, Rodrigo Gonzalez wrote: On 03/21/2014 09:29 PM, john.tiger wrote: EDIT from @rodrigo hmm, user postgres vs os postgres - okay understand what you mean but how is this fixed ? or what is proper procedure ? It depends on what you want to fix... if you want to be able to do su - postgres change its OS password sudo passwd postgres or use sudo su - postgres and you just need your password (in case you can use sudo but you can according to your original email) About socket... Check that it is listening on that socket if debian is the same than ubuntu (I only have access to ubuntu right now) grep unix_socket /etc/postgresql/9.3/main/postgresql.conf Of course change 9.3 with your version or jsut ls /etc/postgresql and you will see which one you have installed there I hope this helps Best regards Rodrigo Gonzalez uncommented postgresql.conf sline: listen addresses = localhost still getting the "is server running error" Did you restart the server after making the config change? Also please do not paraphrase the error messages, cut and paste the entire message into the email. not sure what's wrong here - we have 9.3 running on a number of debian machines (both stable and testing ) - did something change in testing ? -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.3 debian install setup failure
On 03/21/2014 06:43 PM, Rodrigo Gonzalez wrote: On 03/21/2014 09:29 PM, john.tiger wrote: EDIT from @rodrigo hmm, user postgres vs os postgres - okay understand what you mean but how is this fixed ? or what is proper procedure ? It depends on what you want to fix... if you want to be able to do su - postgres change its OS password sudo passwd postgres or use sudo su - postgres and you just need your password (in case you can use sudo but you can according to your original email) About socket... Check that it is listening on that socket if debian is the same than ubuntu (I only have access to ubuntu right now) grep unix_socket /etc/postgresql/9.3/main/postgresql.conf Of course change 9.3 with your version or jsut ls /etc/postgresql and you will see which one you have installed there I hope this helps Best regards Rodrigo Gonzalez uncommented postgresql.conf sline: listen addresses = localhost still getting the "is server running error" not sure what's wrong here - we have 9.3 running on a number of debian machines (both stable and testing ) - did something change in testing ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.3 debian install setup failure
On 03/21/2014 09:29 PM, john.tiger wrote: > EDIT from @rodrigo > > hmm, user postgres vs os postgres - okay understand what you mean but > how is this fixed ? or what is proper procedure ? > It depends on what you want to fix... if you want to be able to do su - postgres change its OS password sudo passwd postgres or use sudo su - postgres and you just need your password (in case you can use sudo but you can according to your original email) About socket... Check that it is listening on that socket if debian is the same than ubuntu (I only have access to ubuntu right now) grep unix_socket /etc/postgresql/9.3/main/postgresql.conf Of course change 9.3 with your version or jsut ls /etc/postgresql and you will see which one you have installed there I hope this helps Best regards Rodrigo Gonzalez -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.3 debian install setup failure
thks for quick comments - see below: On 03/21/2014 06:00 PM, Adrian Klaver wrote: On 03/21/2014 04:47 PM, john.tiger wrote: 9.3 install on debian jessie 64amd sudo -u postgres psql => worked alter user postgres with password 'password' => alter role su - postgress enter password => authentication failure So did you really type postgress (note extra s) or is this just an email error? email error hba.conf edited to allow "trust" to local - shouldn't this be set up as standard default ? This is a packager decision, in other words something Debian did. I didn't mean it was pre-set to trust - actually pre set to "peer" - I set it to "trust" to try to fix failure - no luck - it did not work but do think default should be set to "md5" or "trust" but based on mail list msgs maybe md5 is better than trust still authentication failure (after rebooting) now sudo -u postgres psql => could not connect - is server running - shouldn't install set up server to be booted on start up ? You would think. Are you sure it is not running? In other words have you done a ps ax and looked to see if it is running. It could also be that it running a listening on a different port. It would be helpful if you showed the actual full error messages. The fragment you show above (if I am assuming right) usually asks if the server is running and listening on port sorry - trying to install on another machine not running email - "connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432" ps aux => shows it running btw - trying to install this on chromebook running crouton - wonder if this is causing EDIT from @rodrigo hmm, user postgres vs os postgres - okay understand what you mean but how is this fixed ? or what is proper procedure ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.3 debian install setup failure
On 03/21/2014 08:47 PM, john.tiger wrote: > 9.3 install on debian jessie 64amd > > sudo -u postgres psql => worked > alter user postgres with password 'password' => alter role > > su - postgress > enter password => authentication failure postgress or postgres? The name is different, but in case you made a typo here, with alter user you changed postgresql password for user postgres not for OS user postgres > > hba.conf edited to allow "trust" to local - shouldn't this be set up as > standard default ? Other people can give you different replies...for me...NO, security is first and trust is completely skipping security from my point of view > > still authentication failure (after rebooting) > > now sudo -u postgres psql => could not connect - is server running - > shouldn't install set up server to be booted on start up ? Is it running? I dont use debian, but something like service postgresql status can give you an idea... or, more general ps aux | grep postgres Best regards Rodrigo Gonzalez -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.3 debian install setup failure
On 03/21/2014 04:47 PM, john.tiger wrote: 9.3 install on debian jessie 64amd sudo -u postgres psql => worked alter user postgres with password 'password' => alter role su - postgress enter password => authentication failure So did you really type postgress (note extra s) or is this just an email error? hba.conf edited to allow "trust" to local - shouldn't this be set up as standard default ? This is a packager decision, in other words something Debian did. still authentication failure (after rebooting) now sudo -u postgres psql => could not connect - is server running - shouldn't install set up server to be booted on start up ? You would think. Are you sure it is not running? In other words have you done a ps ax and looked to see if it is running. It could also be that it running a listening on a different port. It would be helpful if you showed the actual full error messages. The fragment you show above (if I am assuming right) usually asks if the server is running and listening on port -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.3 debian install setup failure
9.3 install on debian jessie 64amd sudo -u postgres psql => worked alter user postgres with password 'password' => alter role su - postgress enter password => authentication failure hba.conf edited to allow "trust" to local - shouldn't this be set up as standard default ? still authentication failure (after rebooting) now sudo -u postgres psql => could not connect - is server running - shouldn't install set up server to be booted on start up ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Please post: Surge 2014 CFP is Open
Surge 2014 dates are September 24-26, 2014; the CFP is open and the deadline is March 31, 2014. Thank you, --Katherine
[GENERAL] configure errors on Fedora 20
Hi All; when I try to compile postgresql 9.3.4 I get the below errors, anyone know if / which devel packages I need? Thanks in advance checking crypt.h usability... no checking crypt.h presence... yes configure: WARNING: crypt.h: present but cannot be compiled configure: WARNING: crypt.h: check for missing prerequisite headers? configure: WARNING: crypt.h: see the Autoconf documentation configure: WARNING: crypt.h: section "Present But Cannot Be Compiled" configure: WARNING: crypt.h: proceeding with the preprocessor's result configure: WARNING: crypt.h: in the future, the compiler will take precedence configure: WARNING: ## ## configure: WARNING: ## Report this to pgsql-b...@postgresql.org ## configure: WARNING: ## ## checking for crypt.h... yes checking dld.h usability... no checking dld.h presence... no checking for dld.h... no checking fp_class.h usability... no checking fp_class.h presence... no checking for fp_class.h... no checking getopt.h usability... no checking getopt.h presence... yes configure: WARNING: getopt.h: present but cannot be compiled configure: WARNING: getopt.h: check for missing prerequisite headers? configure: WARNING: getopt.h: see the Autoconf documentation configure: WARNING: getopt.h: section "Present But Cannot Be Compiled" configure: WARNING: getopt.h: proceeding with the preprocessor's result configure: WARNING: getopt.h: in the future, the compiler will take precedence configure: WARNING: ## ## configure: WARNING: ## Report this to pgsql-b...@postgresql.org ## configure: WARNING: ## ## checking for getopt.h... yes checking ieeefp.h usability... no checking ieeefp.h presence... no checking for ieeefp.h... no checking ifaddrs.h usability... no checking ifaddrs.h presence... yes configure: WARNING: ifaddrs.h: present but cannot be compiled configure: WARNING: ifaddrs.h: check for missing prerequisite headers? configure: WARNING: ifaddrs.h: see the Autoconf documentation configure: WARNING: ifaddrs.h: section "Present But Cannot Be Compiled" configure: WARNING: ifaddrs.h: proceeding with the preprocessor's result configure: WARNING: ifaddrs.h: in the future, the compiler will take precedence configure: WARNING: ## ## configure: WARNING: ## Report this to pgsql-b...@postgresql.org ## configure: WARNING: ## ## checking for ifaddrs.h... yes checking langinfo.h usability... yes checking langinfo.h presence... yes checking for langinfo.h... yes checking poll.h usability... no checking poll.h presence... yes configure: WARNING: poll.h: present but cannot be compiled configure: WARNING: poll.h: check for missing prerequisite headers? configure: WARNING: poll.h: see the Autoconf documentation configure: WARNING: poll.h: section "Present But Cannot Be Compiled" configure: WARNING: poll.h: proceeding with the preprocessor's result configure: WARNING: poll.h: in the future, the compiler will take precedence configure: WARNING: ## ## configure: WARNING: ## Report this to pgsql-b...@postgresql.org ## configure: WARNING: ## ## checking for poll.h... yes checking pwd.h usability... yes checking pwd.h presence... yes checking for pwd.h... yes checking sys/ioctl.h usability... no checking sys/ioctl.h presence... yes configure: WARNING: sys/ioctl.h: present but cannot be compiled configure: WARNING: sys/ioctl.h: check for missing prerequisite headers? configure: WARNING: sys/ioctl.h: see the Autoconf documentation configure: WARNING: sys/ioctl.h: section "Present But Cannot Be Compiled" configure: WARNING: sys/ioctl.h: proceeding with the preprocessor's result configure: WARNING: sys/ioctl.h: in the future, the compiler will take precedence configure: WARNING: ## ## configure: WARNING: ## Report this to pgsql-b...@postgresql.org ## configure: WARNING: ## ## checking for sys/ioctl.h... yes checking sys/ipc.h usability... no checking sys/ipc.h presence... yes configure: WARNING: sys/ipc.h: present but cannot be compiled configure: WARNING: sys/ipc.h: check for missing prerequisite headers? configure: WARNING: sys/ipc.h: see the Autoconf documentation configure: WARNING: sys/ipc.h: section "Present But Cannot Be Compiled" configure: WARNING: sys/ipc.h: proceeding with the preprocessor's result configure: WARNING: sys/ipc.h: in the future, the compiler will take precedence configure: WARNING: ## ## configure: WARNING: ## Report this to pgsql-b...@postgresql.org ## configure: WARNING: ##
Re: [GENERAL] Upgrade: 9.0.5->9.3.4 [RESOLVED]
On Fri, 21 Mar 2014, Rich Shepard wrote: psql -e -d template1 -f /home/rshepard/data/database-backups/pg-9.0.5-2014-03-17.sql Yep. Now all databases are running on 9.3.4. Thanks, all! Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Introducing walctl, a simple tool for safe WAL management
https://github.com/peak6/wallctl Oops. I just renamed this to: https://github.com/peak6/walctl I didn't realize the repo was created named wallctl instead of walctl. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Introducing walctl, a simple tool for safe WAL management
Hello again, Now that the open-source floodgates have been opened, I've been prepping a couple of our applicable projects. This time, I've added this: https://github.com/peak6/wallctl It is a very simple PostgreSQL WAL management system that pushes or pulls WAL files from a remote central storage server. It can be used to fully replace archive_command or restore_command in handling WAL archival or recovery. It's designed to basically force WAL storage on a system that isn't a master or standby server. The primary reasoning is that clusters may have several replicas, so why expect the master to service all of them? Master systems push WAL files to the remote archive, and slaves pull from it; done. It sets up each newly created clone as a streaming replica. In case of disconnection, it uses restore_command to fetch the remote WAL files to keep the slaves current. Something of a poor-man's self-healing. Anyway, give it a look. I'll be the one looking over pull requests, so if you have some improvements, I'd be glad to see them. Later! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Passing array of range literals
Glenn Pierce wrote
> I cannot work out how to pass a literal for the array of timerange types.
> '{(15:11:21, 18:11:21)}'::timerange[] does not work for example.
> Also I can not pass NULL for this parameter I get
since NULL can take on any type if you pass it literally you have to specify
the type you need:
NULL::timerange[]
Though I'd suggest passing in an empty array instead:
ARRAY[]::timerange[]
Note this is also the best way to construct the array:
ARRAY['(15:11:21,18:11:21)','(19.11.22,21:12:17]']::timerange[]
Otherwise you need to use double-quotes somewhere. Once you construct an
array as above just cast it to text and let PostgreSQL tell you what the
text representation would look like if you cannot use the ARRAY[] form.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Passing-array-of-range-literals-tp5797031p5797062.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] General Advice for avoiding concurrency during schema migrations
Hi there, I was just wondering if anyone has some general advice for how to ensure a schema migration for an application has exclusivity during its run. This is to avoid silly things like, if someone leaves an application server running during migration the migration should be able to lock somehow to avoid any changes while it is running. I've taken a look at LOCK TABLE, which can lock a table obviously (and I can obviously lock _all_ tables to fake a LOCK DATABASE), but I'm wondering if someone has some opinion around just raising the transaction isolation level to achieve similar goals? Any lessons learnt and best practices would be much appreciated :-). ken. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade: 9.0.5->9.4
On Fri, 21 Mar 2014, Rich Shepard wrote: 1.) Start the new -9.4 version running. It's now running. 2.) Run pg_restore on each database that's in the .sql file created by pg_dumpall. Will this work? psql -e -d template1 -f /home/rshepard/data/database-backups/pg-9.0.5-2014-03.17.sql (all on one line, of course; wrapped by alpine.) Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade: 9.0.5->9.4
On Fri, 21 Mar 2014, Rich Shepard wrote: I did a pg_dumpall before starting this process. If I can use pg_restore on the running 9.4 will that convert locales and get the job done? Let me be more specific. Can I get databases working again by doing these steps? 1.) Start the new -9.4 version running. 2.) Run pg_restore on each database that's in the .sql file created by pg_dumpall. I have 5 databases in the file, but only need to restore 4 to fully working condition. If there's another process please tell me how to proceed. Thanks, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade: 9.0.5->9.4
On Fri, 21 Mar 2014, Joshua D. Drake wrote: If you want to use pg_upgrade, then you are going to have to dump your old database, clean it to UTF-8 (with say iconv), then reimport it. At that point you might as well just pull it into 9.3. Joshua, I did a pg_dumpall before starting this process. If I can use pg_restore on the running 9.4 will that convert locales and get the job done? Thanks, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade: 9.0.5->9.4
On Fri, 21 Mar 2014, Sergey Konoplev wrote: Drop your newly created 9.4 cluster dir and re-init it with C locale like this: Sergey, How do I drop the cluster? I've not had need to do this before. initdb --locale=C -D ... Do I want the former locale or the current one; does it make any difference for the few databases in the cluster? Thanks, Rich -- Richard B. Shepard, Ph.D. | Have knowledge, will travel. Applied Ecosystem Services, Inc. | www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade: 9.0.5->9.4
On 03/21/2014 09:34 AM, Rich Shepard wrote: Postgresql-9.0.5 is installed in /usr/local/pgsql/ and -9.4's data/ subdirectory is installed in /var/lib/pgsql/9.4/ with the executable in /usr/bin/. Running pg_upgrade fails at the end because of a locale difference: lc_ctype cluster values do not match: old "C", new "en_US.UTF-8" Failure, exiting How do I remedy this? If you want to use pg_upgrade, then you are going to have to dump your old database, clean it to UTF-8 (with say iconv), then reimport it. At that point you might as well just pull it into 9.3. JD Thanks in advance, Rich -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc Political Correctness is for cowards. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade: 9.0.5->9.4
On Fri, Mar 21, 2014 at 9:34 AM, Rich Shepard wrote: > Postgresql-9.0.5 is installed in /usr/local/pgsql/ and -9.4's data/ > subdirectory is installed in /var/lib/pgsql/9.4/ with the executable in > /usr/bin/. > > Running pg_upgrade fails at the end because of a locale difference: > > lc_ctype cluster values do not match: old "C", new "en_US.UTF-8" > Failure, exiting > > How do I remedy this? Drop your newly created 9.4 cluster dir and re-init it with C locale like this: initdb --locale=C -D ... -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Upgrade: 9.0.5->9.4
Postgresql-9.0.5 is installed in /usr/local/pgsql/ and -9.4's data/ subdirectory is installed in /var/lib/pgsql/9.4/ with the executable in /usr/bin/. Running pg_upgrade fails at the end because of a locale difference: lc_ctype cluster values do not match: old "C", new "en_US.UTF-8" Failure, exiting How do I remedy this? Thanks in advance, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Passing array of range literals
Hi
I am try to create a function that returns true if a timestamp is within
working hours.
The function will take the following parameters.
1, timestamp - The timestamp I want to check
2, days_of_week - An array of integers that tells us what days are work
days.
3, time_ranges - An array of my custom timerange type. Allows one to pass
an array of hours in a day that are not work hours.
3, date_ranges - An array of daterange types. Allows one to pass say date
ranges of school holidays.
Something like
CREATE TYPE timerange AS RANGE (
subtype = TIME with time zone
);
CREATE OR REPLACE FUNCTION check_within_working_hours(ts timestamptz,
days_of_week integer[], time_ranges timerange[], date_ranges daterange[])
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
SELECT extract(dow from $1) = ANY (days_of_week) INTO passed;
IF passed THEN
RETURN passed;
END IF;
return 'f';
END;
$$ LANGUAGE plpgsql;
I have a large table with timestamp ts and double value. I was going to
call the function above like
SELECT * FROM sensor_values WHERE check_within_working_hours(ts,
'{1,2,3}'::integer[], '{}'::timerange[], NULL) LIMIT 10;
This works but I have a few problems.
I cannot work out how to pass a literal for the array of timerange types.
'{(15:11:21, 18:11:21)}'::timerange[] does not work for example.
Also I can not pass NULL for this parameter I get
ERROR: function check_within_working_hours(timestamp with time zone,
integer[], unknown, unknown) is not unique
Once I can pass the parameters I need the sql to check my passed timestamp
is within the array of timeranges or dateranges.
Does anyone know what the most efficient means to achieve that is ?
Also should I investigate creating this as a c function or will it be ok
performance wise?
Thanks for any advice
Re: [GENERAL] Dead rows not getting removed during vacuum
On Fri, Mar 21, 2014 at 2:46 AM, Granthana Biswas wrote: > We don't have two-phase commit prepared transactions. During vacuum, along > with this problem we are also facing streaming replication issue. Hundreds of > xlogs are in ready state in pg_xlog/archive_status. Those are being copied > manually after vacuum otherwise rsync happens very slowly and replication lag > keeps increasing. Okay, I see. Then next questions are: What postgres version is it? What max_standby_archive_delay and max_standby_streaming_delay are set to? Are there any long running activity or idling in transaction backends on your slaves? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY error with null date
It is taking date as an empty string. Try defining the empty field as '\N' in your source file. ::DISCLAIMER:: The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects.
Re: [GENERAL] Dead rows not getting removed during vacuum
Hi Sergey, We don't have two-phase commit prepared transactions. During vacuum, along with this problem we are also facing streaming replication issue. Hundreds of xlogs are in ready state in pg_xlog/archive_status. Those are being copied manually after vacuum otherwise rsync happens very slowly and replication lag keeps increasing. Regards, Granthana On Fri, Mar 21, 2014 at 3:36 AM, Sergey Konoplev wrote: > On Thu, Mar 20, 2014 at 5:27 AM, Granthana Biswas > wrote: > > Has anyone ever faced the issue of dead rows not getting removed during > > vacuum even if there are no open transactions/connections? > > What does the pg_prepared_xacts view show? > > -- > Kind regards, > Sergey Konoplev > PostgreSQL Consultant and DBA > > http://www.linkedin.com/in/grayhemp > +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 > gray...@gmail.com >
Re: [GENERAL] Dead rows not getting removed during vacuum
Hi Venkata, No there are no other connections to the DB during vacuum. Regards, Granthana On Fri, Mar 21, 2014 at 3:12 AM, Venkata Balaji Nagothi wrote: > On Thu, Mar 20, 2014 at 5:27 AM, Granthana Biswas wrote: > > Hello All, >> >> Has anyone ever faced the issue of dead rows not getting removed during >> vacuum even if there are no open transactions/connections? >> >> We have been facing this during every scheduled vacuum which is done >> after closing all other database connections: >> >> 119278 dead row versions cannot be removed yet. >> >> This means there are transactions in progress when the vacuum was > running. Do you see any such situation ? > > Please let us know, which version of PostgreSQL is this. > > Venkata Balaji N > > Sr. Database Administrator > Fujitsu Australia > >
