[GENERAL] Why SyncOneBuffer does not called frequently?
Hi all: I am trying to understand when the bgwriter is written. I thought that the bgwriter.c's calling turn is: BackgroundWriterMain -BgBufferSync- SyncOneBuffer And In my postgresql.conf , the bgwriter_delay=200ms. I did the following: postgres=# select * from testtab; id | val +--- 1 | 12345 (1 row) postgres=# update testtab set val='54321' where id=1; UPDATE 1 postgres=# select * from testtab; id | val +--- 1 | 54321 (1 row) postgres=# Now I can say the buffer is dirty ,right? I wait for a few minutes, I can found bgwriter's BackgroundWriterMain called BgBufferSync many times. But I can't find BgBufferSync really call SyncOneBuffer to put the dirty data todisk. Untill I close the postgres process, I can find the SyncOneBuffer is called for many times. My question is: Why even there are dirty buffer(s), the SyncOneBuffer is still not called? Is it violating the background writer's purpose? Or the flushing to disk will be done untill the amount of block/buffer is satisfied? If so , what is it? Thanks in advance for any help
[GENERAL] Parallel Insert and Delete operation
Hi All, Please clarify me the following example. I have 2 tables Table1 - ( it has one primary key and few columns ) Table2 - ( it has one primary key and few columns. It has one foreign key, which refers table1 primary key ). I have 2 operations, which are written in pl/pgsql procedure. Operation1() - Inserts the records to table2 at every hour basis. Operation2() - Delete the records from Table 1 and Table2 based on the primary key. What if both operations are running at the time for the same primary key. what I have to take care to run these two operations perfectly without creating inconsistency in database. Thanks Regards, Ramkumar
Re: [GENERAL] Average Balance life
Hi, On Tuesday, October 30, 2012 8:12:25 PM UTC+1, David Johnston wrote: Start learning about Window functions/clauses: http://www.postgresql.org/docs/9.2/interactive/tutorial-window.html The lag function over a window ordered by date will allow you to calculate how many days since the last transaction. Will do, thanks for the tip. You sample data is simplistic to the point of being unusable. (...) But given that most accounts have numerous debits and credits flowing through them the logic by which you choose the endpoints is unclear but fundamental to the solution you seek. At first blush you seem to need to decide whether you want to deal with FIFO, LIFO, or specific-lots. I am trying to find accounting errors in the style of: credits/debits put into wrong accounts (ie: payments to providers without bills, etc), payrolls with typing errors... So if I know, for example, that all payroll accounts go to 0 two days after getting credited (it's Average Balance Life would be 2 days) I want to see which accounts do not comply ie: part or the whole balance is not debited on the two days. Reading the LAG function it seems it may help me to catch the most simple cases (ie: those with sequential credit / debit movements). Do you have some other identifier (i.e., control) attached to these amounts that would aid in choosing the endpoints? Nope. Anyway I just thought that ANOTHER way to look at it would be If account balance was going to 0 and now it is not without having reached 0, alert this seems like a far simpler approach that would detect the same kind of errors I'm looking for (I'd have to restart set the balance to 0 on every alert, etc). I'll work on that one which smells like some of this window function stuff. Thanks, marc -- 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] Parallel Insert and Delete operation
Yelai, Ramkumar IN BLR STS worte: Sent: Wednesday, October 31, 2012 12:40 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Parallel Insert and Delete operation Hi All, Please clarify me the following example. I have 2 tables Table1 - ( it has one primary key and few columns ) Table2 - ( it has one primary key and few columns. It has one foreign key, which refers table1 primary key ). I have 2 operations, which are written in pl/pgsql procedure. Operation1() - Inserts the records to table2 at every hour basis. Operation2() - Delete the records from Table 1 and Table2 based on the primary key. What if both operations are running at the time for the same primary key. what I have to take care to run these two operations perfectly without creating inconsistency in database. With the foreign key in place there can be no entry in table2 that does not have a corresponding entry in table1. Concurrency is solved with locks, so one of the concurrent operations might have to wait until the other one is done. That is handled by the database system automatically. Yours, Laurenz Albe -- 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 setup chained CA?
Hello Tom, Per your recommendation, I tried to append reverse order of certs. 1. On postgresql server side 1) create a self-signed root certificate 2) create an intermediate cert signed by root certificate 3) create a server.crt signed by the intermediate cert 4) append the intermediate cert to server.crt 5) append the root cert to server.crt 6) start up postgresql server 2. On postgresql client side 1) copy the self signed root.crt from postgresql server 2) create a postgresql.crt signed by root.crt 3) tried to connect to the postgresql server using psql 4) STILL FAILED with same error. I tried server.crt signed by root.crt and postgresql.crt signed by the intermediate cert and appending intermediate certs and root.crt to postgresql.crt. This didn't work either. Anything wrong or missing in these steps? Thank you, Choon Park On Tue, Oct 30, 2012 at 7:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: ChoonSoo Park luisp...@gmail.com writes: Then I tried to test more complex thing - chained CA. Scenario 1. Postgresql having server.crt signed by Root CA and one of clients having postgresql.crt signed by intermediate CA. Machine 1: Created a new intermediate CA (ra.crt) signed by root certificate. Created a new client certificate signed by the intermediate CA. Concatenated root CA intermediate CA using openssl x509 -text -in root.crt newroot.crt openssl x509 -text -in ra.crt newroot.crt Not an SSL expert, but my recollection is that the order of the certs in the file is significant, and this order is the wrong one: root cert goes last. Moreover, root.crt should basically only contain the trusted root cert. The chains of intermediate certs (plus a copy of the root cert) belong in server.crt and the client-side postgresql.cert. Not terribly good design, probably, but you'd have to take that up with the openssl folk not us. FWIW, I *have* tested chained certs, and they do work for me per the documentation; or at least did the last time I tried it about two years ago. regards, tom lane
[GENERAL] Boolean type storage format
What is the data physically stored as for boolean type? I know that it is one byte but is it char, int, or something else? Sent from my smartphone
Re: [GENERAL] Parallel Insert and Delete operation
It is also possible that you will get a foreign key violation exception on the process inserting into table 2, but you will not get database inconsistency. On Wed, Oct 31, 2012 at 9:33 AM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Yelai, Ramkumar IN BLR STS worte: Sent: Wednesday, October 31, 2012 12:40 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Parallel Insert and Delete operation Hi All, Please clarify me the following example. I have 2 tables Table1 - ( it has one primary key and few columns ) Table2 - ( it has one primary key and few columns. It has one foreign key, which refers table1 primary key ). I have 2 operations, which are written in pl/pgsql procedure. Operation1() - Inserts the records to table2 at every hour basis. Operation2() - Delete the records from Table 1 and Table2 based on the primary key. What if both operations are running at the time for the same primary key. what I have to take care to run these two operations perfectly without creating inconsistency in database. With the foreign key in place there can be no entry in table2 that does not have a corresponding entry in table1. Concurrency is solved with locks, so one of the concurrent operations might have to wait until the other one is done. That is handled by the database system automatically. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
Re: [GENERAL] Why SyncOneBuffer does not called frequently?
On Wed, Oct 31, 2012 at 1:53 AM, 高健 luckyjack...@gmail.com wrote: Hi all: I am trying to understand when the bgwriter is written. I thought that the bgwriter.c's calling turn is: BackgroundWriterMain -BgBufferSync- SyncOneBuffer SyncOneBuffer is called with skip_recently_used true, so the buffer will not get written by the background writer if it is was recently used. And In my postgresql.conf , the bgwriter_delay=200ms. I did the following: postgres=# select * from testtab; id | val +--- 1 | 12345 (1 row) postgres=# update testtab set val='54321' where id=1; UPDATE 1 postgres=# select * from testtab; id | val +--- 1 | 54321 (1 row) postgres=# Now I can say the buffer is dirty ,right? It is dirty, but it also has a positive usagecount. Are you sure it is that SyncOneBuffer is not getting called, rather than it is getting called but returning before doing the write? Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] When do archived WAL files gets removed after wal_keep_segments changed?
Hello I run two PostgreSQL servers in a master-slave setup and set wal_keep_segments=1000 on the master to allow long downtimes on the slave. Meanwhile the disk got fuller than I estimated and I changed the config to wal_keep_segments=500 and restarted the server afterwards. Yet, the number of WAL segments in my archive dir was still at 1000! I tried a random mix of VACUUM, CHECKPOINT, pg_start_backup(), pg_stop_backup(), server restarts, googling and just plain waiting but the number of archived WAL segments just increased to 1018. Although I understand that I may safely delete those WAL files that are older than the last .backup file I wonder if that shouldn't be happen automatically. Why doesn't it? Relevant non-default settings from the master with version 9.1.6: wal_level = hot_standby archive_mode = on archive_command = 'test ! -f /srv/postgresql-archivedir/%f cp %p /srv/postgresql-archivedir/%f' wal_keep_segments = 500 hot_standby = on bye, -christian- -- 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] Delay streaming replication using a paramter
On Wed, Oct 31, 2012 at 1:01 PM, Mahmoud Hakeem-Habeeb mahmoud.hakeem.hab...@gmail.com wrote: Hi, Firstly is it possible for me to delay streaming replication using wal_sender_delay or any other parameter. No, you can't do delayed replication on streaming. You can do it if you use file based replication only. Secondly is it possible or under what circumstance will a slave get corrupted from the master either a table of a database. This is the reason i want to find out if it is possible to delay replication incase of any corruption of data not propagating to the slave. I don't understand this question, so I can't answer it. Sorry. //Magnus
[GENERAL] dropdb breaks replication?
I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? Thanks, Edson -- 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] dropdb breaks replication?
On Wed, Oct 31, 2012 at 10:32 AM, Edson Richter edsonrich...@hotmail.com wrote: I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? How did you determine that replication was broken, and how did you manually synchronize the servers? Are you certain that replication was working prior to dropping the database? -- 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] dropdb breaks replication?
Em 31/10/2012 15:39, Lonni J Friedman escreveu: On Wed, Oct 31, 2012 at 10:32 AM, Edson Richter edsonrich...@hotmail.com wrote: I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? How did you determine that replication was broken, and how did you manually synchronize the servers? Are you certain that replication was working prior to dropping the database? I'm sure replication was running. I usually keep two windows open in both servers, running In master: watch -n 2 ps aux | egrep sender In slave: watch -n 2 ps aux | egrep receiver At the point the dropdb command has been executed, both disappeared from my radar. Also, in the log there is the following error: LOG: replicação em fluxo conectou-se com sucesso ao servidor principal FATAL: não pôde receber dados do fluxo do WAL: FATAL: segmento do WAL solicitado 0001000100BE já foi removido May the cause not having enough segments (currently 80) for dropdb command? Is dropdb logged in transaction log page-by-page excluded? Thanks, Edson -- 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] Boolean type storage format
On Wed, Oct 31, 2012 at 8:52 PM, Alexander Gataric gata...@usa.net wrote: What is the data physically stored as for boolean type? I know that it is one byte but is it char, int, or something else? False represented by zero bytes and True by 1 byte with value 1. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] dropdb breaks replication?
On Wed, Oct 31, 2012 at 11:01 AM, Edson Richter edsonrich...@hotmail.com wrote: Em 31/10/2012 15:39, Lonni J Friedman escreveu: On Wed, Oct 31, 2012 at 10:32 AM, Edson Richter edsonrich...@hotmail.com wrote: I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? How did you determine that replication was broken, and how did you manually synchronize the servers? Are you certain that replication was working prior to dropping the database? I'm sure replication was running. I usually keep two windows open in both servers, running In master: watch -n 2 ps aux | egrep sender In slave: watch -n 2 ps aux | egrep receiver At the point the dropdb command has been executed, both disappeared from my radar. Also, in the log there is the following error: LOG: replicação em fluxo conectou-se com sucesso ao servidor principal FATAL: não pôde receber dados do fluxo do WAL: FATAL: segmento do WAL solicitado 0001000100BE já foi removido May the cause not having enough segments (currently 80) for dropdb command? Is dropdb logged in transaction log page-by-page excluded? I can't read portugese(?), but i think the gist of the error is that the WAL segment was already removed before the slave could consume it. I'm guessing that you aren't keeping enough of them, and dropping the database generated a huge volume which flushed out the old ones before they could get consumed by your slave. -- 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] Boolean type storage format
It would also matter what columns were next to it, correct? For example, if you had 4 bools in a row, that could also be 1 byte.. On Wed, Oct 31, 2012 at 11:08 AM, Raghavendra raghavendra@enterprisedb.com wrote: On Wed, Oct 31, 2012 at 8:52 PM, Alexander Gataric gata...@usa.netwrote: What is the data physically stored as for boolean type? I know that it is one byte but is it char, int, or something else? False represented by zero bytes and True by 1 byte with value 1. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] dropdb breaks replication?
Lonni J Friedman netll...@gmail.com writes: On Wed, Oct 31, 2012 at 11:01 AM, Edson Richter edsonrich...@hotmail.com wrote: May the cause not having enough segments (currently 80) for dropdb command? Is dropdb logged in transaction log page-by-page excluded? I can't read portugese(?), but i think the gist of the error is that the WAL segment was already removed before the slave could consume it. I'm guessing that you aren't keeping enough of them, and dropping the database generated a huge volume which flushed out the old ones before they could get consumed by your slave. dropdb generates one, not very large, WAL record saying go rm -rf this directory. So sheer WAL volume is not the correct explanation. It's possible though that the slave spent long enough executing the rm -rf to fall behind the master. In any case, it should have been able to catch up automatically if WAL archiving was configured properly. 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] dropdb breaks replication?
Em 31/10/2012 16:09, Lonni J Friedman escreveu: On Wed, Oct 31, 2012 at 11:01 AM, Edson Richter edsonrich...@hotmail.com wrote: Em 31/10/2012 15:39, Lonni J Friedman escreveu: On Wed, Oct 31, 2012 at 10:32 AM, Edson Richter edsonrich...@hotmail.com wrote: I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? How did you determine that replication was broken, and how did you manually synchronize the servers? Are you certain that replication was working prior to dropping the database? I'm sure replication was running. I usually keep two windows open in both servers, running In master: watch -n 2 ps aux | egrep sender In slave: watch -n 2 ps aux | egrep receiver At the point the dropdb command has been executed, both disappeared from my radar. Also, in the log there is the following error: LOG: replicação em fluxo conectou-se com sucesso ao servidor principal FATAL: não pôde receber dados do fluxo do WAL: FATAL: segmento do WAL solicitado 0001000100BE já foi removido May the cause not having enough segments (currently 80) for dropdb command? Is dropdb logged in transaction log page-by-page excluded? I can't read portugese(?), but i think the gist of the error is that the WAL segment was already removed before the slave could consume it. I'm guessing that you aren't keeping enough of them, and dropping the database generated a huge volume which flushed out the old ones before they could get consumed by your slave. Sorry for the portguese text. Yes, your assumption is correct: WAL segment has been excluded before being able to replicate. I keep 80 WAL segments, but I was wondering if a drop database is being logged: it's just so fast, I thought it wasn't logged. And what is the purpose to log (and replicate) the database drop, if you will not be able to recover it - IMHO, dropdb should be replicated as database deactivation or something more or like that... Edson -- 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] dropdb breaks replication?
Em 31/10/2012 16:34, Tom Lane escreveu: Lonni J Friedman netll...@gmail.com writes: On Wed, Oct 31, 2012 at 11:01 AM, Edson Richter edsonrich...@hotmail.com wrote: May the cause not having enough segments (currently 80) for dropdb command? Is dropdb logged in transaction log page-by-page excluded? I can't read portugese(?), but i think the gist of the error is that the WAL segment was already removed before the slave could consume it. I'm guessing that you aren't keeping enough of them, and dropping the database generated a huge volume which flushed out the old ones before they could get consumed by your slave. dropdb generates one, not very large, WAL record saying go rm -rf this directory. So sheer WAL volume is not the correct explanation. It's possible though that the slave spent long enough executing the rm -rf to fall behind the master. Your assumption is right: the slave server is a slow mono processor, low memory, cloud computer, and would have taken very long time to delete everything. In any case, it should have been able to catch up automatically if WAL archiving was configured properly. I don't use WAL archiving - both servers are miles away from each other, and don't have anything except PostgreSQL async replication over VPN connecting them. Edson 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] dropdb breaks replication?
On 10/31/12 11:34 AM, Edson Richter wrote: Sorry for the portguese text. Yes, your assumption is correct: WAL segment has been excluded before being able to replicate. I keep 80 WAL segments, but I was wondering if a drop database is being logged: it's just so fast, I thought it wasn't logged. And what is the purpose to log (and replicate) the database drop, if you will not be able to recover it - IMHO, dropdb should be replicated as database deactivation or something more or like that... WAL is not a 'redo' log like Oracle uses. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unable to do a mailing list proper search
Good afternoon all, I am trying to search the mailing list archives for the phrase list all role privileges, and I would like to return only the matches containing that exact phrase, and nothing else. Yet nothing works. No matter what I enter, I get all pages that contain any or all of the words, whether or not they are are concurrent to each other, or spread throughout the page. Sure, the results include all pages with the exact phrase I want, but searching through all the unwanted pages is frustratingly tedious. Can anyone tell me what the secret is to obtaining the desired search results? Enclosing a phrase in quotes works with most mailing list archives, with most search engines, and in queries. So surely there is a way to perform a similar search on the PostgreSQL archives? Thanks, Craig Sent - Gtek Web Mail -- 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] Unable to do a mailing list proper search
On 10/31/2012 12:59 PM, cr...@gtek.biz wrote: Good afternoon all, I am trying to search the mailing list archives for the phrase list all role privileges, and I would like to return only the matches containing that exact phrase, and nothing else. Yet nothing works. No matter what I enter, I get all pages that contain any or all of the words, whether or not they are are concurrent to each other, or spread throughout the page. Sure, the results include all pages with the exact phrase I want, but searching through all the unwanted pages is frustratingly tedious. Can anyone tell me what the secret is to obtaining the desired search results? Enclosing a phrase in quotes works with most mailing list archives, with most search engines, and in queries. So surely there is a way to perform a similar search on the PostgreSQL archives? Use Nabble search? http://postgresql.1045698.n5.nabble.com/ Thanks, Craig Sent - Gtek Web Mail -- Adrian Klaver adrian.kla...@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
Re: [GENERAL] Unable to do a mailing list proper search
On 10/31/2012 12:59 PM, cr...@gtek.biz wrote: list all role privileges Google: site:archives.postgresql.org 'list all role privileges' -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- 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] Delay streaming replication using a paramter
You would like to delay the replication of data from the master to the slave in order to prevent the possibility of corrupted data being sent to the slave? I don't understand this strategy. Can you explain? On Wed, Oct 31, 2012 at 8:01 AM, Mahmoud Hakeem-Habeeb mahmoud.hakeem.hab...@gmail.com wrote: Hi, Firstly is it possible for me to delay streaming replication using wal_sender_delay or any other parameter. Secondly is it possible or under what circumstance will a slave get corrupted from the master either a table of a database. This is the reason i want to find out if it is possible to delay replication incase of any corruption of data not propagating to the slave. Thanks -- MAHH Network/System Analyst 07788174559 -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
Re: [GENERAL] dropdb breaks replication?
Edson -- I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? Sorry for the slow response -- as others have indicated, the drop db is probably not the problem. We have one system that drops a several-gig database hourly and the replication has never failed. We see issues on the master with dead file handles but the replication itself is rock solid. Greg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] role does not exist
I have successfully installed PostgreSQL on a Ubuntu Linux machine. However right off the bat I type 'psql' and I get the error: 'role does not exist'. Where xxx is the user name logged in. How do I overcome this hurdle. Right now all of our data is on a Windows SQL Server. Can someone give me step by step directions on how to import the databases from SQL Server. Actually I only want a few tables in the database. Hints? Thank you.
Re: [GENERAL] role does not exist
On 10/31/2012 03:54 PM, Kevin Burton wrote: I have successfully installed PostgreSQL on a Ubuntu Linux machine. However right off the bat I type ‘psql’ and I get the error: ‘role “” does not exist’. Where xxx is the user name logged in. How do I overcome this hurdle. Right now all of our data is on a Windows SQL Server. Can someone give me step by step directions on how to import the databases from SQL Server. Actually I only want a few tables in the database. Hints? http://www.postgresql.org/docs/9.2/interactive/app-psql.html Usage Connecting to a Database The default user name is your Unix user name, as is the default database name. To specify a user other than your system user name: -U username --username=username Connect to the database as the user username instead of the default. (You must have permission to do so, of course.) This assumes you have that user set up. Otherwise see: http://www.postgresql.org/docs/9.2/interactive/app-createuser.html Thank you. -- Adrian Klaver adrian.kla...@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
Re: [GENERAL] role does not exist
On 10/31/2012 03:54 PM, Kevin Burton wrote: I have successfully installed PostgreSQL on a Ubuntu Linux machine. However right off the bat I type ‘psql’ and I get the error: ‘role “” does not exist’. Where xxx is the user name logged in. How do I overcome this hurdle. Right now all of our data is on a Windows SQL Server. Can someone give me step by step directions on how to import the databases from SQL Server. Actually I only want a few tables in the database. Hints? Well step by step is a bit exhaustive for a mailing list. To solve the specific question the default user is postgres and the default super user within postgresql is postgres. Try this: sudo -u postgres psql JD Thank you. -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- 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] Why SyncOneBuffer does not called frequently?
HI,
I just debug the code, find that the following stack will call the
SyncOneBuffer.
postgres.exe!SyncOneBuffer(int buf_id=2, char skip_recently_used=0) Line
1640C
postgres.exe!BufferSync(int flags=64) Line 1284 + 0xb bytesC
postgres.exe!CheckPointBuffers(int flags=64) Line 1801 + 0x9 bytesC
postgres.exe!CheckPointGuts(XLogRecPtr checkPointRedo={...}, int flags=64)
Line 8129 + 0x9 bytesC
postgres.exe!CreateCheckPoint(int flags=64) Line 7977 + 0x11 bytesC
postgres.exe!CheckpointerMain() Line 505 + 0x9 bytesC
postgres.exe!AuxiliaryProcessMain(int argc=2, char * * argv=0x002f6fb8)
Line 429C
postgres.exe!SubPostmasterMain(int argc=4, char * * argv=0x002f6fb0) Line
4136 + 0x13 bytesC
postgres.exe!main(int argc=4, char * * argv=0x002f6fb0) Line 176 + 0xd
bytesC
postgres.exe!__tmainCRTStartup() Line 582 + 0x19 bytesC
postgres.exe!mainCRTStartup() Line 399C
This is the check point background process.
--
ThanksRegards,
Xiong He
-- Original --
From: luckyjack...@gmail.com;
Date: Wed, Oct 31, 2012 04:53 PM
To: pgsql-generalpgsql-general@postgresql.org;
Subject: [GENERAL] Why SyncOneBuffer does not called frequently?
Hi all:
I am trying to understand when the bgwriter is written.
I thought that the bgwriter.c's calling turn is:
BackgroundWriterMain -BgBufferSync- SyncOneBuffer
And In my postgresql.conf , the bgwriter_delay=200ms.
I did the following:
postgres=# select * from testtab;
id | val
+---
1 | 12345
(1 row)
postgres=# update testtab set val='54321' where id=1;
UPDATE 1
postgres=# select * from testtab;
id | val
+---
1 | 54321
(1 row)
postgres=#
Now I can say the buffer is dirty ,right?
I wait for a few minutes, I can found bgwriter's BackgroundWriterMain called
BgBufferSync many times.
But I can't find BgBufferSync really call SyncOneBuffer to put the dirty data
todisk.
Untill I close the postgres process, I can find the SyncOneBuffer is called for
many times.
My question is:
Why even there are dirty buffer(s), the SyncOneBuffer is still not called?
Is it violating the background writer's purpose?
Or the flushing to disk will be done untill the amount of block/buffer is
satisfied? If so , what is it?
Thanks in advance for any help
Re: [GENERAL] dropdb breaks replication?
Em 31/10/2012 20:47, Greg Williamson escreveu: Edson -- I've two PostgreSQL 9.1.6 running on Linux CentOS 5.8 64bit. They are replicated asynchronously. Yesterday, I've dropped a database of 20Gb, and then replication has broken, requiring me to manually synchronize both servers again. It is expected that dropdb (or, perhaps, createdb) break existing replication between servers? Sorry for the slow response -- as others have indicated, the drop db is probably not the problem. We have one system that drops a several-gig database hourly and the replication has never failed. We see issues on the master with dead file handles but the replication itself is rock solid. Greg Our application should (almost) never delete databases, but just in case I'll keep an eye open, and manually sync the replication if needed. It is not a major issue, was more a matter of curiosity. Also, John pointed that xlog in PostgreSQL is not the same as the concept I had from Oracle days. Thanks, Greg (and everyone). Edson -- 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] Why SyncOneBuffer does not called frequently?
This perhaps proves that the bgwriter doesn't need to call the SyncOneBuffer again on the same buffer.--ThanksRegards,Xiong He--Original--From: "Xiong He"iih...@qq.com;Date: Thu, Nov 1, 2012 07:40 AMTo: "高健"luckyjack...@gmail.com; "pgsql-general"pgsql-general@postgresql.org; Subject: Re: [GENERAL] Why SyncOneBuffer does not called frequently?HI, I just debug the code, find that the following stack will call the SyncOneBuffer. postgres.exe!SyncOneBuffer(int buf_id=2, char skip_recently_used=0) Line 1640 C postgres.exe!BufferSync(int flags=64) Line 1284 + 0xb bytes C postgres.exe!CheckPointBuffers(int flags=64) Line 1801 + 0x9 bytes C postgres.exe!CheckPointGuts(XLogRecPtr checkPointRedo={...}, int flags=64) Line 8129 + 0x9 bytes C postgres.exe!CreateCheckPoint(int flags=64) Line 7977 + 0x11 bytes C postgres.exe!CheckpointerMain() Line 505 + 0x9 bytes C postgres.exe!AuxiliaryProcessMain(int argc=2, char * * argv=0x002f6fb8) Line 429 C postgres.exe!SubPostmasterMain(int argc=4, char * * argv=0x002f6fb0) Line 4136 + 0x13 bytes C postgres.exe!main(int argc=4, char * * argv=0x002f6fb0) Line 176 + 0xd bytes C postgres.exe!__tmainCRTStartup() Line 582 + 0x19 bytes C postgres.exe!mainCRTStartup() Line 399 CThis is the check point background process.--ThanksRegards,Xiong He-- Original --From: "高健"luckyjack...@gmail.com;Date: Wed, Oct 31, 2012 04:53 PMTo: "pgsql-general"pgsql-general@postgresql.org; Subject: [GENERAL] Why SyncOneBuffer does not called frequently?Hi all:I am trying to understand when the bgwriter is written.I thought that the bgwriter.c's calling turn is:BackgroundWriterMain -BgBufferSync- SyncOneBuffer
And In my postgresql.conf , the bgwriter_delay=200ms.I did the following:postgres=# select * from testtab;id | val +---
1 | 12345(1 row)postgres=# update testtab set val='54321' where id=1;UPDATE 1postgres=# select * from testtab;id | val +---
1 | 54321(1 row)postgres=# Now I can say the buffer is dirty ,right?I wait for a few minutes, I can found bgwriter's BackgroundWriterMain called BgBufferSync many times.
But I can't find BgBufferSync really call SyncOneBuffer to put the dirty data todisk.Untill I close the postgres process, I can find the SyncOneBuffer is called for many times.
My question is: Why even there are dirty buffer(s), the SyncOneBuffer is still not called? Is it violating the background writer's purpose? Or the flushing to disk will be done untill the amount of block/buffer is satisfied? If so , what is it?
Thanks in advance for any help
Re: [GENERAL] Why PGDLLIMPORT is needed
You can check the macro definition there: #ifdef BUILDING_DLL #define PGDLLIMPORT __declspec (dllexport) #else/* not BUILDING_DLL */ #define PGDLLIMPORT __declspec (dllimport) #endif #ifdef _MSC_VER #define PGDLLEXPORT __declspec (dllexport) #else #define PGDLLEXPORT #endif It's only useful on Windows platform for the dll building(import, export) the API entries. -- ThanksRegards, Xiong He -- Original -- From: luckyjack...@gmail.com; Date: Mon, Oct 29, 2012 02:05 PM To: pgsql-generalpgsql-general@postgresql.org; Subject: [GENERAL] Why PGDLLIMPORT is needed On /src/include/storage/proc.h: I saw the following line: extern PGDLLIMPORT PGPROC *MyProc; I want to know why PGDLLIMPORT is used here? Does it mean: exten PGPROC *MyProc; right?
Re: [GENERAL] role does not exist
I tried this and I get an error that 'psql: FATAL: role postgres does not exist' -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Joshua D. Drake Sent: Wednesday, October 31, 2012 6:33 PM To: Kevin Burton Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] role does not exist On 10/31/2012 03:54 PM, Kevin Burton wrote: I have successfully installed PostgreSQL on a Ubuntu Linux machine. However right off the bat I type 'psql' and I get the error: 'role does not exist'. Where xxx is the user name logged in. How do I overcome this hurdle. Right now all of our data is on a Windows SQL Server. Can someone give me step by step directions on how to import the databases from SQL Server. Actually I only want a few tables in the database. Hints? Well step by step is a bit exhaustive for a mailing list. To solve the specific question the default user is postgres and the default super user within postgresql is postgres. Try this: sudo -u postgres psql JD Thank you. -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- 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] role does not exist
On 10/31/2012 06:01 PM, Kevin Burton wrote: I tried this and I get an error that 'psql: FATAL: role postgres does not exist' What is the content of /etc/postgresql/pg_hba.conf? -- Adrian Klaver adrian.kla...@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
Re: [GENERAL] role does not exist
What am I looking for? It is full of comments. If I am looking at the lines that don't begin with '#' I only see all and postgres as users. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver Sent: Wednesday, October 31, 2012 8:42 PM To: Kevin Burton Cc: 'Joshua D. Drake'; pgsql-general@postgresql.org Subject: Re: [GENERAL] role does not exist On 10/31/2012 06:01 PM, Kevin Burton wrote: I tried this and I get an error that 'psql: FATAL: role postgres does not exist' What is the content of /etc/postgresql/pg_hba.conf? -- Adrian Klaver adrian.kla...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Corrupt Incrementally Updated Backup: missing pg_clog file
Hi all, I have a problem with a corrupt backup, fortunately I was only testing so I did not loose any data. Unfortunetely what I did is to follow the backup guidelines in the documentation, which I thought should work reliably. Here are the details: I am running a postgreSQL 8.4 database on a Debian Squeeze system. For Backups I am using the warm standby and Incrementally Updated Backup method as described in chapter 24.4 of the documentation. So my Setup is as follows: Server 1 (Main): PostgreSQL 8.4 Database with archive_mode enabled shipping WAL files to a NFS drive. Size of database is about 370 GB and growing. Server 2 (Replica): PostgreSQL 8.4 Database in recovery mode. Using pg_standby in recovery.conf and getting WAL files from Server 1 NFS drive. All this works fine and runs without errors. The replica is backed up once a week using rsync, a full backup runs about 10 hours, so I also keep at least 24h of WAL files to make sure I have a consistent backup. The backup process also runs fine without errors, only the time (10h) it takes is quite long, so I decided to test the backup: 1) Restored the full backup to a test directory (var/lib/postgresql/8.4/test) 2) Copied the configuration of the main server to /etc/postgresql/8.4/test/ Altered port number, paths and turned off archive mode in postgresql.conf. 3) Added a recovery.conf in the test servers data dir. Recovering from my backed up WAL files: restore_command = 'cp /var/postgresql-wal-test/%f %p' 4) Started the test server (pg_ctlcluster 8.4 test start) 5) Waited until recovery was done (everything worked fine until then) 2012-09-25 08:26:41 UTC LOG: database system is ready to accept connections 2012-09-25 08:26:41 UTC LOG: autovacuum launcher started 6) Connected via psql to the database and tried a \d to see my tables which did *not* work! Here is the output: 2012-09-25 08:27:03 UTC ERROR: could not access status of transaction 500185903 2012-09-25 08:27:03 UTC DETAIL: Could not open file pg_clog/01DD: No such file or directory. Also trying to SELECT data from the database tables failed with the same error. The backup is corrupt. So my question is, what went wrong: Obviously as the rsync started it copied everything from the pg_clog (which at this point was until pg_clog/01DC) and then went on for another 10+ hours backing up all the rest of the database. At the time the backup ended, the database content changed but the newer clog files did not go into the backup. When restoring the backup and starting the server, the recovery process started at a point where pg_clog was at state 01DE or even further and thus the data from 01DD was missing. So what I do from now, is an extra daily backup of my clog directory to make sure to have working backups. This is not documented in the postgreSQL documentation, and since the result in not doing so can be quite severe I think you should consider this in future PostgreSQL documentation versions. Regards, Juergen Additional information: The EXACT PostgreSQL version you are running: PostgreSQL 8.4.13 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit How you installed PostgreSQL From Linux distro package management: Debian/Aptitude If so, what repository? deb http://ftp.at.debian.org/debian/ squeeze main non-free contrib deb-src http://ftp.at.debian.org/debian/ squeeze main non-free contrib deb http://security.debian.org/ squeeze/updates main contrib non-free deb-src http://security.debian.org/ squeeze/updates main contrib non-free deb http://ftp.debian.org/debian squeeze-updates main contrib non-free deb-src http://ftp.debian.org/debian squeeze-updates main contrib non-free Changes made to the settings in the postgresql.conf file: name | current_setting --+- version | PostgreSQL 8.4.13 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit archive_command | cp -i %p /var/postgres-wal/%f /dev/null cp -i %p /var/postgres-wal/bak/%f /dev/null gzip /var/postgres-wal/bak/%f archive_mode | on archive_timeout | 0 client_encoding | utf8 effective_cache_size | 1000MB lc_collate | en_US.UTF-8 lc_ctype | en_US.UTF-8 listen_addresses | * log_line_prefix | %t maintenance_work_mem | 256MB max_connections | 100 max_stack_depth | 2MB password_encryption | on port | 5432 server_encoding | UTF8 shared_buffers | 650MB ssl | on synchronous_commit | off TimeZone | UTC work_mem | 40MB Operating system and version Linux distro and version: Debian 6.0.6 (squeeze) Kernel details: Linux wegc203094 2.6.32-5-686 #1 SMP Sun May 6 04:01:19 UTC 2012 i686 GNU/Linux What program you're using to connect to PostgreSQL: psql and
Re: [GENERAL] role does not exist
On 11/01/2012 06:54 AM, Kevin Burton wrote: I have successfully installed PostgreSQL on a Ubuntu Linux machine. However right off the bat I type 'psql' and I get the error: 'role does not exist'. As with most Ubuntu packages, there's documentation on post-install setup steps in /usr/share/doc/[packagename]/README.Debian.gz. See: zless /usr/share/doc/postgresql-?.?/README.Debian.gz -- Craig Ringer
[GENERAL] SET PATH / DROP SCHEMA lock conflict
Is there a potential lock conflict between SET PATH and DROP SCHEMA? I've observed (second-hand, so I haven't been able to check pg_locks) sessions piling up on a SET PATH while a DROP SCHEMA ... CASCADE is taking a long time to complete due to a large number of tables in the schema. Best, -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
