Re: [GENERAL] Streaming replication: sequences on slave seemingly ahead of sequences on master
On 4 May 2012 14:55, Vincent de Phily wrote: > It all seems to be working fine, except that when checking the data (selecting > latest primary key and sequence value for all tables) on master and slave, > some sequence ids are higher on the slave than on the master. I could > understand if they were lower, but this is weird. > > * The slave's sequences can be anywhere between 1 and 50 ids ahead. This is normal. The sequences are advanced in chunks of 100, so the master's value will be the nextval() while the value on standby will be the start of the next chunk, so as you say, slightly ahead of the master. The same thing would also happen in case of a crash. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to amend SQL standard to add comments?
Hi. Does anybody here know how to add comments to the SQL standard? I believe this would benefit lots of people. Situation: When a system administrator or database administrator looks at a gnarly SQL query chewing up system resources, there is no way to tell by looking at the query server-side which application it came from, what its purpose is, and who the author or responsible party is. Data: in ANSI SQL standard, you can put single-line comments by preceeding the line with a double-hyphen. These comments will be thrown away by the database client and the server will never see them. Hence the metadata (the data about the query itself) is lost. I propose it'd be a benefit, in today's day of distributed and inter-dependent systems, to pass that data along with the query so that it could be used in troubleshooting if needed. An SQL comment may look something like SELECT STUDENT_ID from STUDENTS WHERE LAST_NAME = 'Smith' and FIRST_NAME = 'Joe' COMMENT 'Query Author: Bob Programmer. Purpose: Pull the student ID number, we'll need it to enroll the student for classes.'; or SELECT STUDENT_ID FROM STUDENTS WHERE LAST_NAME = 'Smith' and FIRST_NAME = 'Joe' COMMENT 'Get the Student ID. Bob Programmer, 9 April 2012. registration.py, line 612'; In the second example, the program that queries the DB can dynamically identify where in the program the query-generating code is located. I'd like to propose such capability be added to the SQL standard... is anybody on this list involved with the SQL standard? What do you think about the idea? Best regards, Aleksey Tsalolikhin -- 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] how to amend SQL standard to add comments?
Aleksey Tsalolikhin wrote on 06.05.2012 19:24: Situation: When a system administrator or database administrator looks at a gnarly SQL query chewing up system resources, there is no way to tell by looking at the query server-side which application it came from, what its purpose is, and who the author or responsible party is. Data: in ANSI SQL standard, you can put single-line comments by preceeding the line with a double-hyphen. These comments will be thrown away by the database client and the server will never see them. Hence the metadata (the data about the query itself) is lost. I propose it'd be a benefit, in today's day of distributed and inter-dependent systems, to pass that data along with the query so that it could be used in troubleshooting if needed. An SQL comment may look something like SELECT STUDENT_ID from STUDENTS WHERE LAST_NAME = 'Smith' and FIRST_NAME = 'Joe' COMMENT 'Query Author: Bob Programmer. Purpose: Pull the student ID number, we'll need it to enroll the student for classes.'; You can use multi-line comments with /* .. */ to send this information to the server: SELECT /* Query Author: Bob Programmer. Purpose: Pull the student ID number, we'll need it to enroll the student for classes */ STUDENT_ID from STUDENTS WHERE LAST_NAME = 'Smith' and FIRST_NAME = 'Joe'; Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there a tool for checking database integrity
Hi, Is there a tool for checking a postgresql database for integrity, so I would be notified immediatly if something went wrong e.g. with memory and can go back to the last good backup? Such a tool would guarantee me I am not sitting on a ticking time bomb (corrupted database) without even noticing it. Thank you in advance, Clemens -- 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] how to amend SQL standard to add comments?
Thomas Kellerer writes: > Aleksey Tsalolikhin wrote on 06.05.2012 19:24: >> Situation: When a system administrator or database administrator looks at >> a gnarly SQL query chewing up system resources, there is no way to tell >> by looking at the query server-side which application it came from, what its >> purpose is, and who the author or responsible party is. >> >> Data: in ANSI SQL standard, you can put single-line comments by preceeding >> the line with a double-hyphen. These comments will be thrown away by the >> database client and the server will never see them. Hence the metadata >> (the data about the query itself) is lost. >> >> I propose it'd be a benefit, in today's day of distributed and >> inter-dependent >> systems, to pass that data along with the query so that it could be used >> in troubleshooting if needed. >> >> An SQL comment may look something like >> >> SELECT STUDENT_ID from STUDENTS >> WHERE LAST_NAME = 'Smith' and FIRST_NAME = 'Joe' >> COMMENT 'Query Author: Bob Programmer. Purpose: Pull the student ID >> number, we'll need it to enroll the student for classes.'; > You can use multi-line comments with /* .. */ to send this information to the > server: Quite aside from the fact that the standard already has two perfectly good comment syntaxes, ISTM that most of the gripe here is about behaviors that are outside the standard's scope. (1) "there is no way to tell by looking at the query server-side which application it came from" ... Really? PG has application_name, and I suspect other SQL databases make more info available than that about where a query came from. The text of the query isn't the only available communication channel, nor necessarily the best one. (2) "These comments will be thrown away by the database client" ... how many cases have you checked? psql does discard single-line comments, which is something maybe we should change; but I'm not aware of any other PG-related clients that do that, and it doesn't seem very likely that clients of other SQL databases do it either. Parsing out comments is a complex, expensive thing and there's seldom a good reason to do it client-side. 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] Is there a tool for checking database integrity
Hi, On 6.5.2012 20:47, Clemens Eisserer wrote: > Hi, > > Is there a tool for checking a postgresql database for integrity, so I > would be notified immediatly if something went wrong e.g. with memory > and can go back to the last good backup? > Such a tool would guarantee me I am not sitting on a ticking time bomb > (corrupted database) without even noticing it. No, at least in the current version. The next version (9.2) will support checksums, but it's meant mostly as a protection against failures at the I/O level. It might catch some memory issues, but it certainly won't be 100% protection. There are unofficial tools (e.g. pg_check @ github, written by me) that perform some checking when requested, but it's not (and never will be) automatic. Moreover, in many cases it's impossible to identify hw-level corruption, unless you take the mainframe approach (running the task on multiple devices and check if they produce the same result). The best thing you can do is: (1) watch the PostgreSQL log for unexpected failures - for example memory issues often manifest themselves as "invalid memory alloc" crashes etc. (2) do regular backups and **check them** i.e. check that the backup finished OK and restore them somewhere else (a backup of a corrupted database usually fails) (3) use good hw (ECC memory, ...) / test it thoroughly etc. kind regards Tomas -- 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] Is there a tool for checking database integrity
On Mon, May 07, 2012 at 12:37:47AM +0200, Tomas Vondra wrote: > Hi, > > On 6.5.2012 20:47, Clemens Eisserer wrote: > > Hi, > > > > Is there a tool for checking a postgresql database for integrity, so I > > would be notified immediatly if something went wrong e.g. with memory > > and can go back to the last good backup? > > Such a tool would guarantee me I am not sitting on a ticking time bomb > > (corrupted database) without even noticing it. > > No, at least in the current version. The next version (9.2) will support > checksums, but it's meant mostly as a protection against failures at the > I/O level. It might catch some memory issues, but it certainly won't be > 100% protection. Postgres 9.2 will _not_ support checksums. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Is there a tool for checking database integrity
On 7.5.2012 00:56, Bruce Momjian wrote: > On Mon, May 07, 2012 at 12:37:47AM +0200, Tomas Vondra wrote: >> Hi, >> >> On 6.5.2012 20:47, Clemens Eisserer wrote: >>> Hi, >>> >>> Is there a tool for checking a postgresql database for integrity, so I >>> would be notified immediatly if something went wrong e.g. with memory >>> and can go back to the last good backup? >>> Such a tool would guarantee me I am not sitting on a ticking time bomb >>> (corrupted database) without even noticing it. >> >> No, at least in the current version. The next version (9.2) will support >> checksums, but it's meant mostly as a protection against failures at the >> I/O level. It might catch some memory issues, but it certainly won't be >> 100% protection. > > Postgres 9.2 will _not_ support checksums. Oh, I see - it was bumped to 9.3 and I've missed that. Sorry for confusion. Tomas -- 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] spanish locale question
Thank you Laurenz for your answer. I think I understand the meaning of ORDER BY my problem is the understanding of COLLATE functionality. I thought that if I collate in es.PE (Spanish) the "o" has the exact value of "ó" so if I INSERT INTO pru VALUES (8, 'leo','zara', 'juan'); How can I obtain this order: id | dad | mum | name +--+--+--- 8 | leo | zara | juan 5 | león | mendoza | jua 2 | leon | mendoza | juan 3 | león | valárd | jose 4 | león | válencia | jos 1 | león | valencia | josé 6 | leon | valencia | josie 7 | león | valencia | josie In the understanding (please forgive I repeat) that "o" and "ó" has no difference in weight in the Spanish collation. As you can see below in the first table of your answer the collation doesn´t work. It assumes that leon is different from león (with accent). I was assuming the collation eliminates such difference. It isn´t that way it works? Thanks in advance for your help. Tulio Albe Laurenz *EXTERN* wrote > > Tulio wrote: >> Let me expand the collate situation. I´m from Perú and I have turned >> everything in postgresql.conf as 'es_PE.UTF-8' even the >> default_text_search_config = 'pg_catalog.spanish'. Even my Ubuntu 12.04 >> works in English I have es_PE locale too. > >> if I do >> >> SELECT * FROM pru order by dad,mum,name; >> >> I get: >> >> id | dad | mum | name >> +--+--+--- >> 2 | leon | mendoza | juan >> 6 | leon | valencia | josie >> 5 | león | mendoza | jua >> 3 | león | valárd | jose >> 1 | león | valencia | josé >> 7 | león | valencia | josie >> 4 | león | válencia | jos >> (7 rows) >> >> Which is a wrong order (collation) in Spanish and I don´t understand why. > > Maybe you misunderstood what it means to ORDER BY multiple > columns. In your query, the rows are ordered by "dad", > then all rows where "dad" is the same are ordered by "mum", > and finally all rows where "dad" and "mum" are the same > are ordered by "name". > > It is explained in the documentation: > http://www.postgresql.org/docs/current/static/queries-order.html > > "When more than one expression is specified, the later values > are used to sort rows that are equal according to the earlier values." > >> But, I noticed that if I do: >> >> SELECT * FROM pru order by dad || mum || name; >> >> I get the correct order: >> >> id | dad | mum | name >> +--+--+--- >> 5 | león | mendoza | jua >> 2 | leon | mendoza | juan >> 3 | león | valárd | jose >> 4 | león | válencia | jos >> 1 | león | valencia | josé >> 6 | leon | valencia | josie >> 7 | león | valencia | josie >> (7 rows) >> >> >> Is this the correct way to order in Postgresql and if it´s not Does >> anyone >> have an idea and could please explain it to me? > > This is not PostgreSQL-specific behaviour, it is defined in the > SQL standard and works like this on all database systems I know. > > You can use the ORDER BY clause you propose if you prefer this > ordering. > > But would you really order 'leon', 'mendoza', 'juan' before > 'leo', 'zara', 'juan'? > > Yours, > Laurenz Albe > > -- > Sent via pgsql-general mailing list (pgsql-general@) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- View this message in context: http://postgresql.1045698.n5.nabble.com/spanish-locale-question-tp5650043p5687242.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] Lost one tablespace - can't access whole database
On Sat, May 5, 2012 at 4:19 PM, Stefan Tzeggai wrote: > Hi > > postgresql 9.1 on Ubuntu 10.04 > > All important information is in the other tablespaces. I would be > totally happy to just loose all relations in that lost tablespace. It's > just indexes. Is there any way to tell PG to drop/ignore that tablespace > and access the database? > > Steve, the reason you're getting those messages when you try to access any tables with SQL is because it is trying to access the indexes in the lost tablespace. I tried recreating your problem on a test server and you do should a few options, which you choose may depend on how big your database is. First, if you haven't already done so, BEFORE DOING ANYTHING ELSE, make a complete file level backup of your database (after shutting it down), less the lost tablespace, of course. There are two types of options that come to mind, there may be others. You should be able to pg_dump your database table by table. I haven't tried it, but I think dumping your databases one by one should work, too, since pg_dump doesn't appear to need to access the missing indexes. pg_dumpall appears to work, too. This gives you several choices, depending upon how many tables had indexes in the lost tablespace. You could, for example, just dump and restore the affected tables. Or you could restore the affected database(s) completely or the entire system from the pg_dumpall file. Another option that seems to work for me is this: 1. Recreate the missing directories in the lost tablspace, specifically the one that starts with "PG_9.1' and the subdirectories under it. The error messages from psql will tell you what their exact names were. 2. Re-index all the tables that had indexes in the lost tablespace. Whichever method you use, you need to re-think your backup protocols. You got lucky here, because there were only index files in the tablespace you lost. Next time you may not be so fortunate. -- Mike Nolan
