Re: [GENERAL] I lost my password
After removing PostgreSQL and manually deleting all the other files installed by it I reinstalled PostgreSQL, I started the cmd.exe and typed psql, asked me for the password, entered it, but to no avail. I took notes of all the information I entered during the installation, so this time I'd be sure of the password. Now I know that the problem resides in the PostgreSQL, this makes it easier to trace the problem; since human error can be out of the way. -Original Message- From: Adrian Klaver Sent: Monday, July 27, 2015 12:12 AM To: p...@arbolone.ca ; PostGreSQL MailingList Subject: Re: [GENERAL] I lost my password On 07/26/2015 08:15 PM, p...@arbolone.ca wrote: OK, I think I should tell you folks that I am a newbie. I am using postgresql to learn the SQL for the purpose of learning storing the data in my c++ application. I have near zero knowledge of SQL or PostgreSQL for that matter. When I type C:\pgsql, I am asked to entered a password, but I don't recall the any passwords I might've set up at installation time. Well I do not use Windows much and not all for Postgres so I am at bit of lose here. I did find this: http://www.enterprisedb.com/docs/en/9.3/pginstguide/PostgreSQL_Installation_Guide-07.htm#P138_8268 which indicates a password is asked for in the install. You have no recollection of that being asked or a clue as to what password you may have used? Since the password is used for both the database and the service account and assuming you have not actually done anything with the database, I take back my earlier comment about not reinstalling. It might be easier to do that then going through all the steps necessary to reset in place, given that you are just starting out. thanks -Original Message- From: Adrian Klaver Sent: Sunday, July 26, 2015 11:09 PM To: p...@arbolone.ca ; PostGreSQL MailingList Subject: Re: [GENERAL] I lost my password On 07/26/2015 08:05 PM, p...@arbolone.ca wrote: I am using windows 8.1 and what I believe to be the latest version of posgresql from postgresql.org. Would it be better to reinstall postgresql? So what password are you talking about? I doubt this requires reinstalling. -Original Message- From: Adrian Klaver Sent: Sunday, July 26, 2015 11:01 PM To: p...@arbolone.ca ; PostGreSQL MailingList Subject: Re: [GENERAL] I lost my password On 07/26/2015 07:51 PM, p...@arbolone.ca wrote: After installing PostgreSQL I lost the password, how can I recover it? What OS? How did you install? What password are you talking about? Assuming you are talking about a Postgres password, take a look at: http://www.postgresql.org/docs/9.4/interactive/auth-pg-hba-conf.html From there you can set the local connection to trust and then access Postgres. From there you can reset the password using ALTER USER: http://www.postgresql.org/docs/9.4/interactive/sql-alteruser.html -- 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 -- 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] Connections closing due to terminating connection due to administrator command
If you are running Linux (please ALWAYS give the O/S ), then this could have been caused by the sys admin doing a system shutdown. Otherwise, anyone that can sudo to postgres could also have potentially issued a pg_ctl stop. I strongly suggest you review the _system_ logs for a possible hint as to whom could have done that. Also, consider adding the user name into the log prefix ( # %u = user name ) On Mon, Jul 27, 2015 at 9:39 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/27/2015 02:16 AM, Herouth Maoz wrote: Hello everybody. In the past week, it has happened to us twice already that we got an exception from our Java application, due to PostgreSQL terminating connection due to administrator command”. The problem is that I’m the administrator, and I issued no such command. On the first opportunity that it happened, the PostgreSQL server actually seemed to have spontaneously shut itself down and restarted: 2015-07-21 15:37:59 IDT LOG: received fast shutdown request 2015-07-21 15:37:59 IDT LOG: aborting any active transactions 2015-07-21 15:37:59 IDT FATAL: terminating connection due to administrator command … 2015-07-21 15:38:01 IDT LOG: shutting down 2015-07-21 15:38:02 IDT LOG: database system is shut down 2015-07-21 15:40:16 IDT LOG: database system was shut down at 2015-07-21 15:38:02 IDT On today’s occasion, the server seems to be continuing as usual, and there is nothing whatsoever in the log. So my questions are: * Given that I did not terminate any backend connection interactively, why did I get a terminating connection due to administrator command” message? Is there any situation where this message is issued without the administrator being involved? This error message comes from intervention by a program external to Postgres: http://www.postgresql.org/message-id/4564.1284559...@sss.pgh.pa.us So what OS and version are you running? What does the Java app do besides accessing the database? Do you have other programs that monitor/control the database? * What could have caused the shutdown on the 21st of July? Again, I did not issue a shutdown request for that server, and the machine didn’t restart. Is there anything in logs before the above that might give a clue? The server is running PostgreSQL 9.1.16. Thank you, Herouth -- 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 -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Postgres SSL connection without client certificates.
On 07/27/2015 12:48 AM, Amit Bondwal wrote: Hi Everyone. I am able to setup postgresql with TLS v1.2 with client certificate. I used below link to setup this and it works nicely. https://www.howtoforge.com/postgresql-ssl-certificates How can I connect to remote database with encrypted connection on tls without setting client certificates on client machine? See here: http://www.postgresql.org/docs/9.4/interactive/auth-pg-hba-conf.html To use client certs you need to set cert for auth-method for a particular connection. So to not use them and have an ssl connection set the connection type to hostssl and set auth-method to something other then cert. I am using linux as a clinet machine and want to connect through command line, without client certificate in encrypted format or on SSL. What should I do? If I understand, you want to a non-ssl connection to Postgres, correct? If so then per the docs in the above link then use host for either local or remote TCP/IP connections or local for a socket connection. -- Thanks Regards, Amit Bondwal Contact me at +91-999-0235-948 -- 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] I lost my password
On 07/27/2015 06:22 AM, p...@arbolone.ca wrote: After removing PostgreSQL and manually deleting all the other files installed by it I reinstalled PostgreSQL, I started the cmd.exe and typed psql, asked me for the password, entered it, but to no avail. I took notes of all the information I entered during the installation, so this time I'd be sure of the password. Now I know that the problem resides in the PostgreSQL, this makes it easier to trace the problem; since human error can be out of the way. Can you show that actual psql command line you are using? If not what user are you connecting as? The password that is set up is for the Postgres superuser 'postgres', so you would need to log in via psql using that user. -Original Message- From: Adrian Klaver Sent: Monday, July 27, 2015 12:12 AM To: p...@arbolone.ca ; PostGreSQL MailingList Subject: Re: [GENERAL] I lost my password On 07/26/2015 08:15 PM, p...@arbolone.ca wrote: OK, I think I should tell you folks that I am a newbie. I am using postgresql to learn the SQL for the purpose of learning storing the data in my c++ application. I have near zero knowledge of SQL or PostgreSQL for that matter. When I type C:\pgsql, I am asked to entered a password, but I don't recall the any passwords I might've set up at installation time. Well I do not use Windows much and not all for Postgres so I am at bit of lose here. I did find this: http://www.enterprisedb.com/docs/en/9.3/pginstguide/PostgreSQL_Installation_Guide-07.htm#P138_8268 which indicates a password is asked for in the install. You have no recollection of that being asked or a clue as to what password you may have used? Since the password is used for both the database and the service account and assuming you have not actually done anything with the database, I take back my earlier comment about not reinstalling. It might be easier to do that then going through all the steps necessary to reset in place, given that you are just starting out. thanks -Original Message- From: Adrian Klaver Sent: Sunday, July 26, 2015 11:09 PM To: p...@arbolone.ca ; PostGreSQL MailingList Subject: Re: [GENERAL] I lost my password On 07/26/2015 08:05 PM, p...@arbolone.ca wrote: I am using windows 8.1 and what I believe to be the latest version of posgresql from postgresql.org. Would it be better to reinstall postgresql? So what password are you talking about? I doubt this requires reinstalling. -Original Message- From: Adrian Klaver Sent: Sunday, July 26, 2015 11:01 PM To: p...@arbolone.ca ; PostGreSQL MailingList Subject: Re: [GENERAL] I lost my password On 07/26/2015 07:51 PM, p...@arbolone.ca wrote: After installing PostgreSQL I lost the password, how can I recover it? What OS? How did you install? What password are you talking about? Assuming you are talking about a Postgres password, take a look at: http://www.postgresql.org/docs/9.4/interactive/auth-pg-hba-conf.html From there you can set the local connection to trust and then access Postgres. From there you can reset the password using ALTER USER: http://www.postgresql.org/docs/9.4/interactive/sql-alteruser.html -- 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] I lost my password
On 27/07/2015 04:15, p...@arbolone.ca wrote: OK, I think I should tell you folks that I am a newbie. I am using postgresql to learn the SQL for the purpose of learning storing the data in my c++ application. I have near zero knowledge of SQL or PostgreSQL for that matter. When I type C:\pgsql, I am asked to entered a password, but I don't recall the any passwords I might've set up at installation time. Hello there, Adrian's advice about re-installing is probably the easiest way to do it. However, here's another route, just for completeness (I'm assuming that you're installing on your own laptop or similar, not a server): Find the file pg_hba.conf in the data directory. Look for a host line like this: host all all 127.0.0.1/32md5 Change md5 to trust, save the file and re-start the PostgreSQL service. You should then be able to connect without a password: psql -U postgres NB - If you don't specify a Postgres user with -U, Postgres assumes that you're connecting as the current operating system user. You can then reset the password for the user postgres (or whatever user you used to connect): alter role postgres with password 'whatever'; Don't forget to edit pg_hba.conf once again and set trust back to md5' (and re-start the service). Finally, note that the postgres user is the super-user, so the usual caveats apply... better to create another ordinary user for normal connections. I hope this helps, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Connections closing due to terminating connection due to administrator command
On 07/27/2015 02:16 AM, Herouth Maoz wrote: Hello everybody. In the past week, it has happened to us twice already that we got an exception from our Java application, due to PostgreSQL terminating connection due to administrator command”. The problem is that I’m the administrator, and I issued no such command. On the first opportunity that it happened, the PostgreSQL server actually seemed to have spontaneously shut itself down and restarted: 2015-07-21 15:37:59 IDT LOG: received fast shutdown request 2015-07-21 15:37:59 IDT LOG: aborting any active transactions 2015-07-21 15:37:59 IDT FATAL: terminating connection due to administrator command … 2015-07-21 15:38:01 IDT LOG: shutting down 2015-07-21 15:38:02 IDT LOG: database system is shut down 2015-07-21 15:40:16 IDT LOG: database system was shut down at 2015-07-21 15:38:02 IDT On today’s occasion, the server seems to be continuing as usual, and there is nothing whatsoever in the log. So my questions are: * Given that I did not terminate any backend connection interactively, why did I get a terminating connection due to administrator command” message? Is there any situation where this message is issued without the administrator being involved? This error message comes from intervention by a program external to Postgres: http://www.postgresql.org/message-id/4564.1284559...@sss.pgh.pa.us So what OS and version are you running? What does the Java app do besides accessing the database? Do you have other programs that monitor/control the database? * What could have caused the shutdown on the 21st of July? Again, I did not issue a shutdown request for that server, and the machine didn’t restart. Is there anything in logs before the above that might give a clue? The server is running PostgreSQL 9.1.16. Thank you, Herouth -- 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] This utility can only upgrade to PostgreSQL version 9.4
I am trying to upgrade from PostgreSQL 9.0.22 to 9.4.4. When I attempt to execute pg_upgrade, I get the following error: Performing Consistency Checks - Checking cluster versions This utility can only upgrade to PostgreSQL version 9.4. The command I am executing is: /app/PostgreSQL/9.4/bin/pg_upgrade -b /app/PostgreSQL/9.0/bin -B /app/PostgreSQL/9.4/bin -c -d /pgsql/stip/data9 -D /pgsql/stip/data94 The data directory for 9.4 is a copy of the data directory for 9.0. I am able to login to the 9.0 data via the 9.0 server and client, so I cannot see how pg_upgrade thinks the data is at a 9.4 level. The error message is not of much help. I have tried searching for an article or document that addresses this particular error message to no avail. Any help you can provide would be greatly appreciated. Thank you, Shane Furlong U.S. Department of Energy Office of Scientific and Technical Information (OSTI) (865) 574-9316, Rm 206-1, office (865) 973-3276 mobile furlo...@osti.govmailto:furlo...@osti.gov
Re: [GENERAL] This utility can only upgrade to PostgreSQL version 9.4
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Shane Furlong asks: When I attempt to execute pg_upgrade, I get the following error: ... This utility can only upgrade to PostgreSQL version 9.4. ... /app/PostgreSQL/9.4/bin/pg_upgrade -b /app/PostgreSQL/9.0/bin -B /app/PostgreSQL/9.4/bin -c -d /pgsql/stip/data9 -D /pgsql/stip/data94 The data directory for 9.4 is a copy of the data directory for 9.0. That is your problem. You need to create a new, empty data directory for 9.4, by using the 9.4 version of initdb. I do agree that the error message could be improved. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201507270953 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlW2OAEACgkQvJuQZxSWSsj1CgCeKU8QO2dEHgBV6Su9D2eJmIss MTkAoIGWD0UYPURhC+2WHU/e44Wcsm5N =IcDc -END PGP SIGNATURE- -- 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] I lost my password
It worked! Thanks kids, that was awesome! One last question, is there a tutorial about accessing the back end using c++ Again, thanks s much! -Original Message- From: Raymond O'Donnell Sent: Monday, July 27, 2015 9:28 AM To: p...@arbolone.ca ; Adrian Klaver ; PostGreSQL MailingList Subject: Re: [GENERAL] I lost my password On 27/07/2015 04:15, p...@arbolone.ca wrote: OK, I think I should tell you folks that I am a newbie. I am using postgresql to learn the SQL for the purpose of learning storing the data in my c++ application. I have near zero knowledge of SQL or PostgreSQL for that matter. When I type C:\pgsql, I am asked to entered a password, but I don't recall the any passwords I might've set up at installation time. Hello there, Adrian's advice about re-installing is probably the easiest way to do it. However, here's another route, just for completeness (I'm assuming that you're installing on your own laptop or similar, not a server): Find the file pg_hba.conf in the data directory. Look for a host line like this: host all all 127.0.0.1/32md5 Change md5 to trust, save the file and re-start the PostgreSQL service. You should then be able to connect without a password: psql -U postgres NB - If you don't specify a Postgres user with -U, Postgres assumes that you're connecting as the current operating system user. You can then reset the password for the user postgres (or whatever user you used to connect): alter role postgres with password 'whatever'; Don't forget to edit pg_hba.conf once again and set trust back to md5' (and re-start the service). Finally, note that the postgres user is the super-user, so the usual caveats apply... better to create another ordinary user for normal connections. I hope this helps, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] This utility can only upgrade to PostgreSQL version 9.4
On 07/27/2015 06:48 AM, Furlong, Shane wrote: I am trying to upgrade from PostgreSQL 9.0.22 to 9.4.4. When I attempt to execute pg_upgrade, I get the following error: *Performing Consistency Checks* *-* *Checking cluster versions* *This utility can only upgrade to PostgreSQL version 9.4. * The command I am executing is: /app/PostgreSQL/9.4/bin/pg_upgrade -b /app/PostgreSQL/9.0/bin -B /app/PostgreSQL/9.4/bin -c -d /pgsql/stip/data9 -D /pgsql/stip/data94 The data directory for 9.4 is a copy of the data directory for 9.0. I am able to login to the 9.0 data via the 9.0 server and client, so I cannot see how pg_upgrade thinks the data is at a 9.4 level. Well there's your problem:) The the new(9.4) data directory needs to have been created by 9.4. Right now pg_upgrade thinks it is doing a 9.0 to 9.0 upgrade. The error message is not telling you it is at 9.4 but at a Postgres other then 9.4. Use the 9.4 initdb or pg_ctl init to create a new data directory that pg_upgrade can move the 9.0 data to. The error message is not of much help. I have tried searching for an article or document that addresses this particular error message to no avail. Any help you can provide would be greatly appreciated. Thank you, *Shane Furlong* U.S. Department of Energy Office of Scientific and Technical Information (OSTI) (865) 574-9316, Rm 206-1, office (865) 973-3276 mobile furlo...@osti.gov mailto:furlo...@osti.gov -- 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] I lost my password
On 27/07/2015 14:54, p...@arbolone.ca wrote: It worked! Thanks kids, that was awesome! Glad it worked. It's a while since I've been called a kid! :-) One last question, is there a tutorial about accessing the back end using c++ I've done it using Qt, and there are tons of documentation and examples out there - Google is your friend. Qt's own documentation is very good. For plain C, read PostgreSQL's own docs: http://www.postgresql.org/docs/9.4/static/libpq.html Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Connections closing due to terminating connection due to administrator command
On 27 ביולי 2015, at 16:39, Adrian Klaver adrian.kla...@aklaver.com wrote: * Given that I did not terminate any backend connection interactively, why did I get a terminating connection due to administrator command” message? Is there any situation where this message is issued without the administrator being involved? This error message comes from intervention by a program external to Postgres: http://www.postgresql.org/message-id/4564.1284559...@sss.pgh.pa.us So what OS and version are you running? OK, that’s a miss on my part, sorry: The OS is Debian GNU/Linux 7. What does the Java app do besides accessing the database? Well, it’s a message-passing application that basically up all the time, processing information sent in by our clients and distributing it. It is not running on the same machine as the database, it is not using a privileged database account (the user is the database’s owner, but not a super-user), and it’s not even running very complex queries. If more detailed information is needed, I’ll provide it as needed, as a full description of the system is going to be very lengthy, and I doubt it will be helpful, given that it’s not really capable of sending signals over to the database process, being on a separate machine. Do you have other programs that monitor/control the database? We have a backup script that runs at 4:02AM every day. Other than that, we just monitor the java program, so if the database fails, it shows up in that program’s log. * What could have caused the shutdown on the 21st of July? Again, I did not issue a shutdown request for that server, and the machine didn’t restart. Is there anything in logs before the above that might give a clue? No, that’s the thing. It’s the first entry in the log for days - the previous log entry is from July 8th. NEW INFORMATION: I believe that today’s mishap is related to the July 21 shutdown problem, based on the logs of our Java program and an inquiry into the way it handles messages that go through a particular path. It seems that one of the modules has a bug and retains stale connections, and since messages that arrive through that path are rare, the first time one came after the July 21st shutdown was today. So I’m left with the question of what caused the shutdown on July 21st. Thank you, Herouth -- 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] Splitting a string containing a numeric value in to three parts
Sanjaya Vithanagama svithanag...@gmail.com wrote:
I can match the numeric value and the rest of the string after
the numeric value using the following:
select regexp_matches('hello+123123.453the-123re',
'([\+|-]?(?:\d*(?:(?:\.)?\d+)))(.*)')
outputs array {+123123.453,the-123re}.
Where I am having trouble is with matching the first part of the
string. In other words what needs to be 'RE' in the following
expression for it to report the all three elements of the array.
select regexp_matches('hello+123123.453the-123re',
'((RE)[\+|-]?(?:\d*(?:(?:\.)?\d+)))(.*)')
should return array {hello,+123123.453,the-123re}.
select regexp_matches('hello+123123.453the-123re',
'^(.*?)([\+|-]?(?:\d*(?:(?:\.)?\d+)))(.*)$')
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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] Delete rule does not prevent truncate
Just in case it has not been made obvious yet, rules are silently deprecated. They still exist because views depend on them, but it is generally considered best practices to not use them outside that realm. Well, the manual doesn't seem to reflect that fact. If that's how the developers feel about rules, i.e they can't be bothered to make any changes to the rules code any more, no matter how minor (e.g. this TRUNCATE issue), then you should explicitly state in the manual that they are depreciated, and stop making stupid statements like so if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that encourage people to use rules ! -- 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] Using the database to validate data
Zdeněk Bělehrádek wrote What about creating a SAVEPOINT before each INSERT, and if the INSERT returns an error, then ROLLBACK TO SAVEPOINT? This way you will have all the insertable data in your table, and you can still ROLLBACK the whole transaction, or COMMIT it if there were no errors. It will probably be quite slow, but if you have only thousands of lines, it should be fast enough for your usecase IMHO. -- Zdeněk Bělehrádek Hmmm, interesting. Thanks, if that works, it would be exactly what I'm looking for! You are right, speed is not an issue. -Jon -- View this message in context: http://postgresql.nabble.com/Using-the-database-to-validate-data-tp5859046p5859239.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] BDR Error on Debian wheezy packages
2015-07-24 14:25:52 UTC [4736-17] LOG: starting background worker process bdr (6174890623557546797,1,20737,)-bdr (6174890623557546797,1, Anyone know how can I solve this error? I'm using BDR replication with Debian wheezy packages. Thanks! 2015-07-24 14:25:52 UTC [4805-1] ERROR: tuple natts mismatch, 15 vs 19 2015-07-24 14:25:52 UTC [4736-18] LOG: worker process: bdr (6174890623557546797,1,20737,)-bdr (6174890623557546797,1, (PID 4805) exited with exit code 1 2015-07-24 14:25:54 UTC [4810-1] postgres@safewalk-server LOG: starting logical decoding for slot bdr_20737_6174890623557546797_1_16388__ 2015-07-24 14:25:54 UTC [4810-2] postgres@safewalk-server DETAIL: streaming transactions committing after 0/28ECC70, reading WAL from 0/28ECB70 2015-07-24 14:25:54 UTC [4810-3] postgres@safewalk-server LOG: logical decoding found consistent point at 0/28ECB70 2015-07-24 14:25:54 UTC [4810-4] postgres@safewalk-server DETAIL: There are no running transactions. 2015-07-24 14:25:54 UTC [4810-5] postgres@safewalk-server LOG: could not receive data from client: Connection reset by peer 2015-07-24 14:25:54 UTC [4810-6] postgres@safewalk-server LOG: unexpected EOF on standby connection -- View this message in context: http://postgresql.nabble.com/BDR-Error-on-Debian-wheezy-packages-tp5859243.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
Re: [GENERAL] Delete rule does not prevent truncate
On 23 July 2015 at 19:25, Scott Marlowe scott.marl...@gmail.com wrote: stick to triggers, they're faster Erm, not according to your beloved manual !!! 38.7. Rules Versus Triggers a rule issuing one extra command is likely to be faster than a trigger The summary is, rules will only be significantly slower than triggers if their actions result in large and badly qualified joins, a situation where the planner fails. -- 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] Connections closing due to terminating connection due to administrator command
On 27 ביולי 2015, at 18:01, Adrian Klaver adrian.kla...@aklaver.com wrote: Not sure what yo have set up for logging, but you might to crank it up. 13 days between entries for a system that is in use all the time seems sort of light to me. Most of the log settings are just the Debian default (except the log prefix). This system is one of several we have, but it’s intended for quick processing of messages by some of our customers that send low volumes but need quick response. As such, it processes around 50,000 messages a day, which boils down to around 200,000 or so database requests a day. It’s very light load, so we don’t get many timeouts or other load-related log entries. I don’t want to log each query, though, as that seems wasteful to me. NEW INFORMATION: I believe that today’s mishap is related to the July 21 shutdown problem, based on the logs of our Java program and an inquiry into the way it handles messages that go through a particular path. It seems that one of the modules has a bug and retains stale connections, and since messages that arrive through that path are rare, the first time one came after the July 21st shutdown was today. So what is the module doing? Or more the point what is the application doing to deal with the stale connections? The module keeps a connection pool available to the threads that call it, one connection per thread. It runs a JDBC prepared statement. If that prepared statement hits an SQLException, it then logs it in two places and attempts to recreate the connection, which is where the bug is. But that bug is just the reason why I didn’t get a fresh connection afterwards, it’s not the reason why the SQLException happened to begin with (which is that the connection kept for this particular thread was from before the shut down). I hope this information helps, Herouth -- 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] Connections closing due to terminating connection due to administrator command
On 07/27/2015 07:45 AM, Herouth Maoz wrote: On 27 ביולי 2015, at 16:39, Adrian Klaver adrian.kla...@aklaver.com wrote: * Given that I did not terminate any backend connection interactively, why did I get a terminating connection due to administrator command” message? Is there any situation where this message is issued without the administrator being involved? This error message comes from intervention by a program external to Postgres: http://www.postgresql.org/message-id/4564.1284559...@sss.pgh.pa.us So what OS and version are you running? OK, that’s a miss on my part, sorry: The OS is Debian GNU/Linux 7. What does the Java app do besides accessing the database? Well, it’s a message-passing application that basically up all the time, processing information sent in by our clients and distributing it. It is not running on the same machine as the database, it is not using a privileged database account (the user is the database’s owner, but not a super-user), and it’s not even running very complex queries. If more detailed information is needed, I’ll provide it as needed, as a full description of the system is going to be very lengthy, and I doubt it will be helpful, given that it’s not really capable of sending signals over to the database process, being on a separate machine. Well what we are talking about is system users not database users, as the error is an indication that some other program then Postgres is shutting down the Postgres server. Do you have other programs that monitor/control the database? We have a backup script that runs at 4:02AM every day. Other than that, we just monitor the java program, so if the database fails, it shows up in that program’s log. * What could have caused the shutdown on the 21st of July? Again, I did not issue a shutdown request for that server, and the machine didn’t restart. Is there anything in logs before the above that might give a clue? No, that’s the thing. It’s the first entry in the log for days - the previous log entry is from July 8th. Not sure what yo have set up for logging, but you might to crank it up. 13 days between entries for a system that is in use all the time seems sort of light to me. NEW INFORMATION: I believe that today’s mishap is related to the July 21 shutdown problem, based on the logs of our Java program and an inquiry into the way it handles messages that go through a particular path. It seems that one of the modules has a bug and retains stale connections, and since messages that arrive through that path are rare, the first time one came after the July 21st shutdown was today. So what is the module doing? Or more the point what is the application doing to deal with the stale connections? So I’m left with the question of what caused the shutdown on July 21st. Thank you, Herouth -- 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] Connections closing due to terminating connection due to administrator command
Herouth Maoz hero...@unicell.co.il writes: So Iâm left with the question of what caused the shutdown on July 21st. Well, you had 2015-07-21 15:37:59 IDT LOG: received fast shutdown request There is exactly one place in the Postgres code that prints that message, and it is the postmaster's SIGINT handler. 2015-07-21 15:37:59 IDT LOG: aborting any active transactions This is just the postmaster noting that it's about to send SIGTERM signals to all its child processes ... 2015-07-21 15:37:59 IDT FATAL: terminating connection due to administrator command ... and here is a child acknowledging receipt of SIGTERM. This is all as-expected once the postmaster's received a shutdown signal. So something sent the postmaster a SIGINT, and it's hard to believe that the source wasn't external to the database. If your application is running on a different machine then it couldn't have been the application's fault. If you didn't issue pg_ctl stop -m fast then we can eliminate that. There has to have been some other software on the database server machine that sent the signal, and it had to have privileges to do so which means it was either running as the postgres O/S user or as root. One thing worth considering is the possibility that the postmaster had been started in such a way that it was still associated with a controlling terminal; then any control-C typed on that terminal would have resulted in a SIGINT. regards, tom lane -- 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] Connections closing due to terminating connection due to administrator command
On 27 ביולי 2015, at 16:55, Melvin Davidson melvin6...@gmail.com wrote: If you are running Linux (please ALWAYS give the O/S ), then this could have been caused by the sys admin doing a system shutdown. Yes, sorry about that, as I previously answered Adrian Klaver, the OS is Debian Gnu/Linux 7. But I did mention that the machine has not been restarted (it currently has a 45 day uptime). And the sysadmin says he didn’t do anything on that machine on that day. Otherwise, anyone that can sudo to postgres could also have potentially issued a pg_ctl stop. The only people with access to that machine are myself (through ssh to the postgres account) and the sysadmin. That machine is basically a dedicated database server. I strongly suggest you review the _system_ logs for a possible hint as to whom could have done that. Also, consider adding the user name into the log prefix ( # %u = user name ) I’ve asked the sysadmin to look at the system logs. I’ll consider the user name suggestion. Thank you. Herouth -- 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] Using the database to validate data
林士博 wrote If I am following correctly, you can do it in your application as follows. 1.begin transaction 2.insert each data. Catch db exception, and save exception message and other information you need to array. 3.in the end ,you can get all the information about the wrong data in array if there is any. and then you can decide whether it is need to rollback or to commit. Yes, I agree that I could do that, which I believe is my IDEA 1 from my original message. This method will naturally work, but it is a very slow iterative process because you can only catch the *first* error, after which new INSERTS are not allowed. If you have a data input with say 1000 record, and there are 50 errors, it would require 50 iterations of fixing the input data, running it again, to find them all. 林士博 wrote By the way, this is about programming but not postgresql. I was hoping that there would be a way to have Postgresql run in a mode where it allows INSERTS within a transaction even after an error. Naturally when the error condition occurs, COMMIT would not be allowed at the end of the transaction block. But, this way, you could collect all the error information in one pass. Seemed postgresql related to me. :) -- View this message in context: http://postgresql.nabble.com/Using-the-database-to-validate-data-tp5859046p5859237.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
Re: [GENERAL] Delete rule does not prevent truncate
For crying out loud GET OVER IT! You've been given a very reasonable and quick solution to your problem. You can either 1. Keep crying and moaning until someone changes the rules. 2. Give up and port to another database. 3. Write the triggers and solve your problem! On Fri, Jul 24, 2015 at 5:27 AM, Tim Smith randomd...@gmail.com wrote: On 23 July 2015 at 19:25, Scott Marlowe scott.marl...@gmail.com wrote: stick to triggers, they're faster Erm, not according to your beloved manual !!! 38.7. Rules Versus Triggers a rule issuing one extra command is likely to be faster than a trigger The summary is, rules will only be significantly slower than triggers if their actions result in large and badly qualified joins, a situation where the planner fails. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Connections closing due to terminating connection due to administrator command
On 07/27/2015 08:42 AM, Herouth Maoz wrote: On 27 ביולי 2015, at 18:01, Adrian Klaver adrian.kla...@aklaver.com wrote: Not sure what yo have set up for logging, but you might to crank it up. 13 days between entries for a system that is in use all the time seems sort of light to me. Most of the log settings are just the Debian default (except the log prefix). This system is one of several we have, but it’s intended for quick processing of messages by some of our customers that send low volumes but need quick response. As such, it processes around 50,000 messages a day, which boils down to around 200,000 or so database requests a day. It’s very light load, so we don’t get many timeouts or other load-related log entries. I don’t want to log each query, though, as that seems wasteful to me. I am not sure that logging every query is necessary. I was thinking more on the lines of connect/disconnect, more info in the log line prefix, moving the log_min_messages down. Anything to provide a little more context. It seems this actual shut down is coming from an external source, but it would be helpful to see if there was a correlation with what is happening in the database and use that to get to causation. NEW INFORMATION: I believe that today’s mishap is related to the July 21 shutdown problem, based on the logs of our Java program and an inquiry into the way it handles messages that go through a particular path. It seems that one of the modules has a bug and retains stale connections, and since messages that arrive through that path are rare, the first time one came after the July 21st shutdown was today. So what is the module doing? Or more the point what is the application doing to deal with the stale connections? The module keeps a connection pool available to the threads that call it, one connection per thread. It runs a JDBC prepared statement. If that prepared statement hits an SQLException, it then logs it in two places and attempts to recreate the connection, which is where the bug is. But that bug is just the reason why I didn’t get a fresh connection afterwards, it’s not the reason why the SQLException happened to begin with (which is that the connection kept for this particular thread was from before the shut down). So how are the stale connections dealt with? I hope this information helps, Herouth -- 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] Connections closing due to terminating connection due to administrator command
On 07/27/2015 08:07 AM, Herouth Maoz wrote: On 27 ביולי 2015, at 16:55, Melvin Davidson melvin6...@gmail.com wrote: If you are running Linux (please ALWAYS give the O/S ), then this could have been caused by the sys admin doing a system shutdown. Yes, sorry about that, as I previously answered Adrian Klaver, the OS is Debian Gnu/Linux 7. But I did mention that the machine has not been restarted (it currently has a 45 day uptime). And the sysadmin says he didn’t do anything on that machine on that day. Otherwise, anyone that can sudo to postgres could also have potentially issued a pg_ctl stop. The only people with access to that machine are myself (through ssh to the postgres account) and the sysadmin. That machine is basically a dedicated database server. I strongly suggest you review the _system_ logs for a possible hint as to whom could have done that. Also, consider adding the user name into the log prefix ( # %u = user name ) I’ve asked the sysadmin to look at the system logs. I’ll consider the user name suggestion. Thank you. Something about this thread rang a bell. Doing some searching I found: http://www.postgresql.org/message-id/6a94a18e-4a2d-4179-83cd-fdbbdf856...@unicell.co.il So how did you deal with the thread topic? Herouth -- 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] pgbench tps drop from 5000 to 37 going from localhost to a server 13ms away
On Mon, Jul 27, 2015 at 3:19 AM, Chris Withers ch...@simplistix.co.uk wrote: On 24/07/2015 22:51, Jeff Janes wrote: starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 This is your problem. There is only one row in the pgbench_branch table, and every transaction has to update that one row. This is inherently a seriaized event. Indeed it was! One solution is to just use a large scale on the benchmark so that they upate random pgbench_branch rows, rather than all updating the same row: pgbench -i -s50 With a scale of 1000, everything except the END took roughly the latency time. Interestingly, the END still seems to take more, when threads/clients are really ramped up (100 vs 8). Why would that be? Could it be the fsync time? Presumably your server has a BBU on it, but perhaps the transaction rate at the high scale factor is high enough to overwhelm it. Are you sure you don't see the same timing when you run pgbench locally? Alternatively, you could write a custom file so that all 7 commands are sent down in one packet. How would you restructure the sql so as the make that happen? Just make a file with all the commands in it, and then remove all the newlines from the non-backslash commands so that they are all on the same line (separated by semicolons). Leave the backslash commands on their own lines. Then use the -f switch to pgbench, giving it the file you just made. Also, make sure you give it '-s 1000' as well, because when you use the -f option pgbench does not auto-detect the scale factor. Cheers, Jeff
Re: [GENERAL] pg_dump error
I am not using any comma here. With the same command I am able to take dump on other disks. But with ISOLON CIFS, I am getting the error. Thanks. On Mon, Jul 27, 2015 at 10:56 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/27/2015 10:55 AM, AI Rumman wrote: No it is -s for schema only backup. My ^ got misplaced in sending. I was talking about the ',' in the file name at the end of the command. Thanks. On Mon, Jul 27, 2015 at 10:53 AM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: On 07/27/2015 10:48 AM, AI Rumman wrote: Hi, I am getting the following error during pg_dump: pg_dump dbname -v -t tablename -s -Fc -f dbname_tablename,sqlc ^ Is that really a ','(comma)? pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used I am using Postgresql 9.1 and I have enough disk space on it. The backup drive is on ISOLON CIFS file system. What may be the errors? Please advice. Thanks. -- Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] pg_dump error
Hi, I am getting the following error during pg_dump: pg_dump dbname -v -t tablename -s -Fc -f dbname_tablename,sqlc pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used I am using Postgresql 9.1 and I have enough disk space on it. The backup drive is on ISOLON CIFS file system. What may be the errors? Please advice. Thanks.
Re: [GENERAL] Using the database to validate data
A little late to the party, but i'll share how I do my data imports / validation for anyone interested. I have a bunch of data that comes in from various sources, and it isn't always guaranteed to be in the correct format, have the right foreign keys, or even the right data types. I have a staging table that is in the format of the feed I have coming in, with all columns text and no constraints at all on the data columns. Example: CREATE TABLE import_sale ( client_id uuid NOT NULL, row_id uuid NOT NULL DEFAULT gen_random_uuid(), row_date timestamp with time zone NOT NULL DEFAULT now(), file_id uuid NOT NULL, sale_number character varying, company_number character varying, invoice_number character varying, invoice_date character varying, order_date character varying, ship_date character varying, sale_date character varying, product_number character varying, quantity character varying, quantity_uom character varying, price character varying, reduction character varying, direct_indicator character varying, redistributor_company_number character varying, freight numeric, processed_ind boolean DEFAULT false, CONSTRAINT import_sales_client_id_fkey FOREIGN KEY (client_id) REFERENCES client (client_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT import_sales_file_id_fkey FOREIGN KEY (file_id) REFERENCES import_file (file_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT import_sales_row_id_unique UNIQUE (row_id) ); I use a talend package, or COPY to get the data into this table. However you want to do that is up to you. I have a final table that I want all this data to eventually get to once there are no issues with it. Example: CREATE TABLE sale ( sale_id uuid NOT NULL DEFAULT gen_random_uuid(), client_id uuid NOT NULL, source_row_id uuid NOT NULL, sale_number character varying NOT NULL, company_id uuid NOT NULL, invoice_number character varying NOT NULL, invoice_date date, order_date date, ship_date date, sale_date date NOT NULL, product_id uuid NOT NULL, quantity numeric NOT NULL, uom_type_id uuid NOT NULL, price numeric NOT NULL, reduction numeric NOT NULL, redistributor_company_id uuid, freight numeric, active_range tstzrange DEFAULT tstzrange(now(), NULL::timestamp with time zone), CONSTRAINT sale_pkey PRIMARY KEY (sale_id), CONSTRAINT sale_client_id_fkey FOREIGN KEY (client_id) REFERENCES client (client_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT sale_company_id_fkey FOREIGN KEY (company_id) REFERENCES company (company_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT sale_product_id_fkey FOREIGN KEY (product_id) REFERENCES product (product_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT sale_redistributor_company_id_fkey FOREIGN KEY (redistributor_company_id) REFERENCES company (company_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT sale_source_row_id_fkey FOREIGN KEY (source_row_id) REFERENCES import_sale (row_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT sale_uom_type_id_fkey FOREIGN KEY (uom_type_id) REFERENCES uom_type (uom_type_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT sale_sale_number_active_range_excl EXCLUDE USING gist (sale_number WITH =, (client_id::character varying) WITH =, active_range WITH ), CONSTRAINT sale_unique UNIQUE (sale_number, client_id, active_range) ); I then have couple functions which run over the data and do the validations / insert / update where necessary. This one validates that the data is able to map to all the foreign keys, the data types can be converted properly, and that not null constraints are enforced. CREATE OR REPLACE FUNCTION import_validate_sale() RETURNS void AS $BODY$ /* Remove any prior exceptions */ DELETE FROM import_sale_error WHERE EXISTS ( SELECT 1 FROM import_sale WHERE import_sale_error.row_id = import_sale.row_id); /* Null checks for required fields */ INSERT INTO import_sale_error(row_id, error_message) SELECT s.row_id, 'sale_number is null, but required.' FROM import_sale s WHERE s.sale_number IS NULL; INSERT INTO import_sale_error(row_id, error_message) SELECT s.row_id, 'distributor company_number is null, but required.' FROM import_sale s WHERE s.company_number IS NULL; INSERT INTO import_sale_error(row_id, error_message) SELECT s.row_id, 'invoice_number is null, but required.' FROM import_sale s WHERE s.invoice_number IS NULL; INSERT INTO import_sale_error(row_id, error_message) SELECT s.row_id, 'sale_date is null, but required.' FROM import_sale s WHERE s.sale_date IS NULL; INSERT INTO import_sale_error(row_id, error_message) SELECT s.row_id,
Re: [GENERAL] pg_dump error
On 07/27/2015 10:58 AM, AI Rumman wrote: I am not using any comma here. With the same command I am able to take dump on other disks. I beg to differ, From your original post: -f dbname_tablename,sqlc ^^^ But with ISOLON CIFS, I am getting the error. Thanks. On Mon, Jul 27, 2015 at 10:56 AM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: On 07/27/2015 10:55 AM, AI Rumman wrote: No it is -s for schema only backup. My ^ got misplaced in sending. I was talking about the ',' in the file name at the end of the command. Thanks. On Mon, Jul 27, 2015 at 10:53 AM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: On 07/27/2015 10:48 AM, AI Rumman wrote: Hi, I am getting the following error during pg_dump: pg_dump dbname -v -t tablename -s -Fc -f dbname_tablename,sqlc ^ Is that really a ','(comma)? pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used I am using Postgresql 9.1 and I have enough disk space on it. The backup drive is on ISOLON CIFS file system. What may be the errors? Please advice. Thanks. -- Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com -- 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] Creating a user for pg_start_backup
* Michael Paquier (michael.paqu...@gmail.com) wrote: On Tue, Jul 21, 2015 at 4:47 PM, Andrew Beverley a...@andybev.com wrote: Dear all, I'm setting up hot backups on my database server. As such, I'd like to set up a Postgres user that has access to only pg_start_backup and pg_stop_backup. I'm unable to work out how to do this with the various GRANT options. Can someone point me in the right direction please? Or is there a better way to achieve this, rather than having a dedicated user? Access to pg_start_backup and pg_stop_backup can be done with either a replication user or a superuser. You can define user with such rights with CREATE ROLE with the keyword REPLICATION: http://www.postgresql.org/docs/devel/static/sql-createrole.html Note that the REPLICATION role gets a great deal more access than simply being able to run pg_start/stop_backup, such as being able to connect to the magic replication database and be able to stream the contents of the database. Would be great to understand your use-case better, to see if the proposed default roles would be a better eventual solution for you. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] pg_dump error
On 07/27/2015 10:48 AM, AI Rumman wrote: Hi, I am getting the following error during pg_dump: pg_dump dbname -v -t tablename -s -Fc -f dbname_tablename,sqlc ^ Is that really a ','(comma)? pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used I am using Postgresql 9.1 and I have enough disk space on it. The backup drive is on ISOLON CIFS file system. What may be the errors? Please advice. Thanks. -- 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] pg_dump error
No it is -s for schema only backup. Thanks. On Mon, Jul 27, 2015 at 10:53 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/27/2015 10:48 AM, AI Rumman wrote: Hi, I am getting the following error during pg_dump: pg_dump dbname -v -t tablename -s -Fc -f dbname_tablename,sqlc ^ Is that really a ','(comma)? pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used I am using Postgresql 9.1 and I have enough disk space on it. The backup drive is on ISOLON CIFS file system. What may be the errors? Please advice. Thanks. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] pg_dump error
On 07/27/2015 10:55 AM, AI Rumman wrote: No it is -s for schema only backup. My ^ got misplaced in sending. I was talking about the ',' in the file name at the end of the command. Thanks. On Mon, Jul 27, 2015 at 10:53 AM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: On 07/27/2015 10:48 AM, AI Rumman wrote: Hi, I am getting the following error during pg_dump: pg_dump dbname -v -t tablename -s -Fc -f dbname_tablename,sqlc ^ Is that really a ','(comma)? pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used I am using Postgresql 9.1 and I have enough disk space on it. The backup drive is on ISOLON CIFS file system. What may be the errors? Please advice. Thanks. -- Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com -- 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] Sorting CSV string and removing Duplicates
I have a csv string in a text field that is unsorted and contains duplicates. Is there a simple way to remove these and sort the string. E.g 2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27 Do you need to eventually load the data in Postgres? I'd personally use python to deal with this, we're talking 4 or 5 lines here, if even. I suspect you can do the same with perl or ruby or whatever is your weapon of choice. How columns does your csv file has? Is it a one-line file? -- http://yves.zioup.com gpg: 4096R/32B0F416 -- 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] Sorting CSV string and removing Duplicates
On Mon, Jul 27, 2015 at 12:57 PM, dinesh kumar dineshkuma...@gmail.com
wrote:
On Mon, Jul 27, 2015 at 12:53 PM, Alex Magnum magnum11...@gmail.com
wrote:
Hello,
I have a csv string in a text field that is unsorted and contains
duplicates.
Is there a simple way to remove these and sort the string.
E.g
2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27
i tried string to array and unique but that did not work...
Any suggestions on how to do this without writing a function?
Any help is appreciated.
Are you looking for this.
postgres=# SELECT unnest(string_to_array(t, ',')) from test group by 1;
unnest
2
18
8
20
22
16
27
17
23
1
(10 rows)
OR
Might be something like this
postgres=# WITH sortedstring as
postgres-# (
postgres(# SELECT unnest(string_to_array(t, ','))::int from test group by 1
ORDER BY 1
postgres(# ) SELECT array_agg(unnest) FROM sortedstring;
array_agg
--
{1,2,8,16,17,18,20,22,23,27}
(1 row)
Regards,
Dinesh
manojadinesh.blogspot.com
Regards,
Dinesh
manojadinesh.blogspot.com
Thanks
A
[GENERAL] Problems with timezone
Hello, I have the following inconsistency in my environment: postgres@postgres =# SELECT current_timestamp; now --- *2015-07-27 16:26:40.001694-03* (1 row) postgres@postgres =# SHOW timezone; TimeZone - Brazil/East (1 row) postgres@postgres =# SELECT blah; ERROR: column blah does not exist In my log file: [*2015-07-27 19:27:10.944 GMT*] 3397 postgres postgres [local] 42703 ERROR: column blah does not exist at character 8 [*2015-07-27 19:27:10.944 GMT*] 3397 postgres postgres [local] 42703 STATEMENT: SELECT blah; My PostgreSQL version is: 9.2.4 cat /etc/issue CentOS release 6.3 (Final) Kernel \r on an \m Could you help me? Thanks a lot. Best regards -- JotaComm http://jotacomm.wordpress.com
Re: [GENERAL] Problems with timezone
On 07/27/2015 12:36 PM, JotaComm wrote: Hello, I have the following inconsistency in my environment: postgres@postgres =# SELECT current_timestamp; now --- *2015-07-27 16:26:40.001694-03* (1 row) postgres@postgres =# SHOW timezone; TimeZone - Brazil/East (1 row) postgres@postgres =# SELECT blah; ERROR: column blah does not exist In my log file: [*2015-07-27 19:27:10.944 GMT*] 3397 postgres postgres [local] 42703 ERROR: column blah does not exist at character 8 [*2015-07-27 19:27:10.944 GMT*] 3397 postgres postgres [local] 42703 STATEMENT: SELECT blah; My PostgreSQL version is: 9.2.4 cat /etc/issue CentOS release 6.3 (Final) Kernel \r on an \m Could you help me? http://www.postgresql.org/docs/9.2/interactive/runtime-config-logging.html log_timezone (string) Sets the time zone used for timestamps written in the server log. Unlike TimeZone, this value is cluster-wide, so that all sessions will report timestamps consistently. The built-in default is GMT, but that is typically overridden in postgresql.conf; initdb will install a setting there corresponding to its system environment. See Section 8.5.3 for more information. This parameter can only be set in the postgresql.conf file or on the server command line. Thanks a lot. Best regards -- JotaComm http://jotacomm.wordpress.com -- 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] Sorting CSV string and removing Duplicates
Hello,
I have a csv string in a text field that is unsorted and contains
duplicates.
Is there a simple way to remove these and sort the string.
E.g
2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27
i tried string to array and unique but that did not work...
Any suggestions on how to do this without writing a function?
Any help is appreciated.
Thanks
A
chris=# SELECT distinct x::int from
unnest(string_to_array('2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27',
',')) x order by x::int;
x
1
2
8
16
17
18
20
22
23
27
(10 rows)
Bye,
Chris.
--
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] Sorting CSV string and removing Duplicates
On Mon, Jul 27, 2015 at 12:53 PM, Alex Magnum magnum11...@gmail.com wrote: Hello, I have a csv string in a text field that is unsorted and contains duplicates. Is there a simple way to remove these and sort the string. E.g 2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27 i tried string to array and unique but that did not work... Any suggestions on how to do this without writing a function? Any help is appreciated. Are you looking for this. postgres=# SELECT unnest(string_to_array(t, ',')) from test group by 1; unnest 2 18 8 20 22 16 27 17 23 1 (10 rows) Regards, Dinesh manojadinesh.blogspot.com Thanks A
[GENERAL] Sorting CSV string and removing Duplicates
Hello, I have a csv string in a text field that is unsorted and contains duplicates. Is there a simple way to remove these and sort the string. E.g 2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27 i tried string to array and unique but that did not work... Any suggestions on how to do this without writing a function? Any help is appreciated. Thanks A
Re: [GENERAL] Lots of stuck queries after upgrade to 9.4
On 07/23/2015 02:36 PM, Heikki Linnakangas wrote: On 07/23/2015 11:31 AM, Spiros Ioannou wrote: Well, so far with commit_delay=0 no problems. I will report back of couse if something happens, but I believe that the problem may indeed be solved/masked with that setting. Rough description of our setup, or how to reproduce: * Timeseries data in table , say, measurements, size: 3-4TB, about 1000 inserts/second * table measurements also has a trigger on insert to also insert on measurements_a (for daily export purposes) Just the above would cause a stuck query after a few days. Now for exporting we run the following CTE query (measurements_b is an empty table, measurements_a has about 5GB) * WITH d_rows AS (DELETE FROM measurement_events_a RETURNING * ) INSERT INTO measurement_events_b SELECT * FROM d_rows; The above caused the problem to appear every time, after a 10-20 minutes. Hmm. With that CTE query, were there other queries running at the same time? I was able to reproduce something like this with pgbench, by running a custom little module that calls the WaitXLogInsertionsToFinish() in a tight loop, and checks that the value it returns moves monotonically forward. With commit_delay on, once every minute or so, it moves backwards. I'll investigate why that happens... I was able to debug the synthetic test case I created, but unfortunately I don't think it explains the lock up you're seeing after all. It's possible for WaitXLogInsertionsToFinish() to move backwards, in this scenario: 1. Backend A acquires WALInsertLock 2, and reserves xlog between byte positions 2100 - 2200 2. Backend B calls WaitXLogInsertionsToFinish(), which blocks on backend A, which hasn't advertised any location yet. 3. Backend C acquires WALInsertLock 1, and reserves xlog between byte positions 2200 - 2300 4. Backend C calls GetXLogBuffer(), and sees that the page is not in cache yet. (It does not call WALInsertLockUpdateInsertingAt() yet, because it's a bit slow or context-switched out) 5. Backend A initializes the page, completes inserting its WAL record, and releases its WALInsertLock. 6. Backend B gets unblocked, seeing that the lock held by B is now free. It calculated 2200 as the return value, which was the latest reserved WAL position. (Backend C started after it began, so it didn't have to wait for it) 7. Backend C calls WALInsertLockUpdateInsertingAt(), with a WAL position pointing to the beginning of the page, 2000. If you now call WALInsertLockUpdateInsertingAt() again, it will return 2000, because backend C is the only backend holding a lock, and its advertised position is 2000. But the previous call calculated 2200. GetXLogBuffer() always advertises a WAL position at the beginning of the requested page, but that's a bit bogus. However, AFAICS that is actually harmless. Backend C is not blocked. The page it's looking for is certainly in cache at this point, so it can continue without blocking. So I don't think this explains your lockup. - Heikki -- 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] Splitting a string containing a numeric value in to three parts
Thank you Kevin! I missed the start of string and end of string matching.
On Tue, Jul 28, 2015 at 1:06 AM, Kevin Grittner kgri...@ymail.com wrote:
Sanjaya Vithanagama svithanag...@gmail.com wrote:
I can match the numeric value and the rest of the string after
the numeric value using the following:
select regexp_matches('hello+123123.453the-123re',
'([\+|-]?(?:\d*(?:(?:\.)?\d+)))(.*)')
outputs array {+123123.453,the-123re}.
Where I am having trouble is with matching the first part of the
string. In other words what needs to be 'RE' in the following
expression for it to report the all three elements of the array.
select regexp_matches('hello+123123.453the-123re',
'((RE)[\+|-]?(?:\d*(?:(?:\.)?\d+)))(.*)')
should return array {hello,+123123.453,the-123re}.
select regexp_matches('hello+123123.453the-123re',
'^(.*?)([\+|-]?(?:\d*(?:(?:\.)?\d+)))(.*)$')
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sanjaya
Re: [GENERAL] pgbench tps drop from 5000 to 37 going from localhost to a server 13ms away
On 24/07/2015 22:51, Jeff Janes wrote: starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 This is your problem. There is only one row in the pgbench_branch table, and every transaction has to update that one row. This is inherently a seriaized event. Indeed it was! One solution is to just use a large scale on the benchmark so that they upate random pgbench_branch rows, rather than all updating the same row: pgbench -i -s50 With a scale of 1000, everything except the END took roughly the latency time. Interestingly, the END still seems to take more, when threads/clients are really ramped up (100 vs 8). Why would that be? Alternatively, you could write a custom file so that all 7 commands are sent down in one packet. How would you restructure the sql so as the make that happen? cheers, Chris
Re: [GENERAL] Sorting CSV string and removing Duplicates
Hi Danish, yes thats the one I was looking for. Thanks a lot!!!
On Tue, Jul 28, 2015 at 1:32 AM, dinesh kumar dineshkuma...@gmail.com
wrote:
On Mon, Jul 27, 2015 at 12:57 PM, dinesh kumar dineshkuma...@gmail.com
wrote:
On Mon, Jul 27, 2015 at 12:53 PM, Alex Magnum magnum11...@gmail.com
wrote:
Hello,
I have a csv string in a text field that is unsorted and contains
duplicates.
Is there a simple way to remove these and sort the string.
E.g
2,18,20,23,1,27,1,2,8,16,17,18,20,22,23,27
i tried string to array and unique but that did not work...
Any suggestions on how to do this without writing a function?
Any help is appreciated.
Are you looking for this.
postgres=# SELECT unnest(string_to_array(t, ',')) from test group by 1;
unnest
2
18
8
20
22
16
27
17
23
1
(10 rows)
OR
Might be something like this
postgres=# WITH sortedstring as
postgres-# (
postgres(# SELECT unnest(string_to_array(t, ','))::int from test group by
1 ORDER BY 1
postgres(# ) SELECT array_agg(unnest) FROM sortedstring;
array_agg
--
{1,2,8,16,17,18,20,22,23,27}
(1 row)
Regards,
Dinesh
manojadinesh.blogspot.com
Regards,
Dinesh
manojadinesh.blogspot.com
Thanks
A
Re: [GENERAL] Using the database to validate data
No, when you catch an insert exception , you can save the information you needed to array or something likes that, and continue to do the next insert. In your application, you can write codes as follows. begin transaction loop try do insert catch exception save error info end loop if have any error print erro rollback transaction else commit transaction or you can you that in postgresql procedure. 2015-07-24 22:51 GMT+09:00 JPLapham lap...@jandr.org: 林士博 wrote If I am following correctly, you can do it in your application as follows. 1.begin transaction 2.insert each data. Catch db exception, and save exception message and other information you need to array. 3.in the end ,you can get all the information about the wrong data in array if there is any. and then you can decide whether it is need to rollback or to commit. Yes, I agree that I could do that, which I believe is my IDEA 1 from my original message. This method will naturally work, but it is a very slow iterative process because you can only catch the *first* error, after which new INSERTS are not allowed. If you have a data input with say 1000 record, and there are 50 errors, it would require 50 iterations of fixing the input data, running it again, to find them all. 林士博 wrote By the way, this is about programming but not postgresql. I was hoping that there would be a way to have Postgresql run in a mode where it allows INSERTS within a transaction even after an error. Naturally when the error condition occurs, COMMIT would not be allowed at the end of the transaction block. But, this way, you could collect all the error information in one pass. Seemed postgresql related to me. :) -- View this message in context: http://postgresql.nabble.com/Using-the-database-to-validate-data-tp5859046p5859237.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 -- ─repica group── ▼ポイント×電子マネー×メールで店舗販促に必要な機能を全て提供！ 【point+plus】http://www.repica.jp/pointplus/ ▼フォローアップメールや外部連携に対応！ 【mail solution】http://ms.repica.jp/ ▼9年連続シェアNo.1 個人情報漏えい対策ソフト 【P-Pointer】http://ppointer.jp/ ▼単月導入可能！AR動画再生アプリ 【marcs】http://www.arappli.com/service/marcs/ ▼ITビジネスを創造しながら未来を創る 【VARCHAR】http://varchar.co.jp/ ───
Re: [GENERAL] Postgres SSL connection without client certificates.
Thanks you very much Adrian for clearing my confusion. On Mon, Jul 27, 2015 at 7:24 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/27/2015 12:48 AM, Amit Bondwal wrote: Hi Everyone. I am able to setup postgresql with TLS v1.2 with client certificate. I used below link to setup this and it works nicely. https://www.howtoforge.com/postgresql-ssl-certificates How can I connect to remote database with encrypted connection on tls without setting client certificates on client machine? See here: http://www.postgresql.org/docs/9.4/interactive/auth-pg-hba-conf.html To use client certs you need to set cert for auth-method for a particular connection. So to not use them and have an ssl connection set the connection type to hostssl and set auth-method to something other then cert. I am using linux as a clinet machine and want to connect through command line, without client certificate in encrypted format or on SSL. What should I do? If I understand, you want to a non-ssl connection to Postgres, correct? If so then per the docs in the above link then use host for either local or remote TCP/IP connections or local for a socket connection. -- Thanks Regards, Amit Bondwal Contact me at +91-999-0235-948 -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] Postgres SSL connection without client certificates.
Hi Everyone. I am able to setup postgresql with TLS v1.2 with client certificate. I used below link to setup this and it works nicely. https://www.howtoforge.com/postgresql-ssl-certificates How can I connect to remote database with encrypted connection on tls without setting client certificates on client machine? I am using linux as a clinet machine and want to connect through command line, without client certificate in encrypted format or on SSL. What should I do? -- Thanks Regards, Amit Bondwal Contact me at +91-999-0235-948
[GENERAL] Connections closing due to terminating connection due to administrator command
Hello everybody. In the past week, it has happened to us twice already that we got an exception from our Java application, due to PostgreSQL terminating connection due to administrator command”. The problem is that I’m the administrator, and I issued no such command. On the first opportunity that it happened, the PostgreSQL server actually seemed to have spontaneously shut itself down and restarted: 2015-07-21 15:37:59 IDT LOG: received fast shutdown request 2015-07-21 15:37:59 IDT LOG: aborting any active transactions 2015-07-21 15:37:59 IDT FATAL: terminating connection due to administrator command … 2015-07-21 15:38:01 IDT LOG: shutting down 2015-07-21 15:38:02 IDT LOG: database system is shut down 2015-07-21 15:40:16 IDT LOG: database system was shut down at 2015-07-21 15:38:02 IDT On today’s occasion, the server seems to be continuing as usual, and there is nothing whatsoever in the log. So my questions are: * Given that I did not terminate any backend connection interactively, why did I get a terminating connection due to administrator command” message? Is there any situation where this message is issued without the administrator being involved? * What could have caused the shutdown on the 21st of July? Again, I did not issue a shutdown request for that server, and the machine didn’t restart. The server is running PostgreSQL 9.1.16. Thank you, Herouth
