[GENERAL] Suppress checking of chmod 700 on data-dir?
Hi postgresql-listmembers, for a backup-scenario I need to have access to the pgdata-directory as a different shell-user, but postgresqul refuses to start if chmod is not 700 on the directory. Is there a way to prevent postgres to check the data-dirs chmod 700 on startup (and while running) ? Thanks for your short replies. I could not figure it out in the documentation. Regards Johannes postgresql 8.2.4 on ubuntu dapper (if this question came 100times, I apologize for being unable to find it) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Suppress checking of chmod 700 on data-dir?
use sudo in your backup scenario, or run you backup as postgres Thanks for your quick reply. Unfortunaltelly runing backup via sudo is not an option due to sercurity issues and using postgres-user is not feasable because other data as well is backuped where postgres-user should not have access to. So your answer means that there is definitelly NO way to circumwent the chmod 700 thing? Its hard to believe that. Each and evera thing is configurable in postgres, but I cannot disable or relax directory-permissions checking? Even not with a compile-option or something like that? Anyway thanks for your help. I'll keep searching for a solution. Regards Johannes ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Suppress checking of chmod 700 on data-dir?
Ragnar wrote: are you planning a filesystem-level backup? are you aware that you cannot just backup the postgres data directories fro under a running server, and expect the backup to be usable? gnari As war as I understood the docu of psql 8.2.4 (http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html section 23.3.2) you can copy the files while postgres is running (respecting pg_start_backup and pg_stop_backup) But that is not my point. The question is where I can change the enforced chmod 700 postgresql always wants me to set. Regards Johannes :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Suppress checking of chmod 700 on data-dir?
Joshua D. Drake wrote: Johannes Konert wrote: But that is not my point. The question is where I can change the enforced chmod 700 postgresql always wants me to set. You can't. You can however change the postgresql.conf to put look for files somewhere besides $PGDATA and thus you would be able to back them up. With postgresql.conf I can change the path to the data-dir, but postgresql checks the chmod 700 on that directory either. So only the logfiles can be written and accessed somewhere else. The data itself is still only accessible by the postgres-user. Anything else in there you should be grabbing via pg_dump anyway. So you suggest not to backup the filesystem-files, but to do a dump instead? Does this work together with PITR as described in 23.3. (http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP). I want to make a full backup every night of a heavy DB while it is running. Combined with short-term-WAL-archiving. Getting the WAL-files is easy by using the config-parameter archive_command, but to copy and backup the "base backup" once a day is impossible if I cannot access the data-files. Will the described backup&restore work as well with a dump + WAL-files? Then I could forget about copying the file-system-files ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Suppress checking of chmod 700 on data-dir?
Oliver Elphick wrote: You could run the backup as postgres and pipe the output to another program owned by the other user and with suid set in its permissions. The suid means that the receiving program would have access where you don't want postgres to go. Thanks Oliver, that was a good hint. Suids are not working on bash-scripts, but with a restricted entry in /etc/sudoers now the backup-user can execute a copy-and-access-script to get the files from within PGDATA-dir. Regards Johannes ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] pg_xlog - files are guaranteed to be sequentialy named?
Hi pgsql-list-members, I currently write a small script that deletes outdated xlog-files from my backup-location. Because I do not want to rely on creation-date, I found it usable to use the result of ln | sort -g -r Thus the newest WAL xlog-file is on top and I can delete all not needed files at the bottom of the list. My question: Is it for ALL cases guaranteed, that the naming of the WAL-files in $PGDATA/pg_xlog always produces a "higher" number for a newer file? What happens if the 24hexdigits reach upper bound? Thank your for your replies on that issue of postgresql inner working model. Regards Johannes ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?
Greg Smith wrote: He's talking about wiping out the ones on the backup server, so I think Johannes means erasing the old archived logs on the secondary here. That can screw up your backup if you do it wrong, but it's not an all-caps worthy mistake. yes, that's what I am talking about related to http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html. Sorry, if that did not came out clearly enough. On Wed, 13 Jun 2007, Johannes Konert wrote: Because I do not want to rely on creation-date, No, you want to rely on creation date, because then this problem goes away. Truely right...if I can gurantee, that the file-dates of my archived WAL-files do have proper timestamps. If the timestamps once are messed up and all have the same timestamp (due to a Windows-copy or something else foolish), then the delete-script might delete the wrong files... The idea you should be working toward is that you identify when your last base backup was started after it's copied to the secondary, and then you can safely delete any archived logs file on the secondary from before that time. Instead of doing "ls | sort -g -r" you should be doing something like looping over the files in a bash shell script and using [ -ot ] to determine which files to delete. right; but as I said, then I rely on file-dates. But during the day I came out with an solution: I store the WAL-files with the time-stamp of archiving in their file-name. Thus I can order and delete them safely. Your hint was the one, that helped me to find that solution - so thanks for that, Greg.....and the others. Regards, Johannes ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?
Johannes Konert wrote: But during the day I came out with an solution: I store the WAL-files with the time-stamp of archiving in their file-name. Thus I can order and delete them safely. Your hint was the one, that helped me to find that solution - so thanks for that, Greg.and the others. That solution has still a problem: It workes fine in case that the WAL-naming restarts with 0001, because the attached timestamp in name would still make it possible to identify the file as being a newer one as , but there is still the problem with shifts in time itself. If someone corrects the servers computer-time/date to a date before current time (e.g. set the clock two hours back), then the newer WAL files will have an older timestamp and will be deleted by accident. Thus now I increase the number of characters of the filename to infinite and the last 24 characters are the WAL file name. Thus the archived filenames ~always~ increase in naming and all backup files before the last base backup can be safely identified not relying on computer timestamps or with the risk of a restart in naming by postgresql. I hope this solutions only border is the 255 character restriction of file-name lengthbut if that one will be reached in future times I am sure longer names are possible :) Regards Johannes ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?
Greg Smith wrote: On Wed, 13 Jun 2007, Johannes Konert wrote: If someone corrects the servers computer-time/date to a date before current time (e.g. set the clock two hours back), then the newer WAL files will have an older timestamp and will be deleted by accident. This should never happen; no one should ever touch the clock by hand on a production system. The primary and backup server should both be syncronized via NTP. If you're thinking about clock changes for daylight savings time, those shouldn't have any effect on timestamps, which should be stored in UTC. If you're on Windows, Its not Windows; it will be Debian Linux. I completely agree with you that of course our servers synchronize themselve via NTP with global time, but we already had the case that - for some reasons - NTP did not work and times drift away from each other. If you have to manage some servers you might not recognize that a NTP daemon does not work anymore or that a new firewall prohibits these TCP packages nowand time goes by, because everything seem to work just fine. Then one nice day you realize, that one, two or many of your servers just have their own time and you need to bring them back to synchronized time while they are online. If you made your applications be aware of such effects and use system-nanotime or global counters where possible, then even these time-corrections can be handled. But I agree with you: of course normally this will never happen...but it happened once. You're working hard to worry about problems that should be eliminated by the overall design of your system. If you can't trust your system clocks and that files are being copied with their attributes intact, you should consider thinking about how to resolve those problems rather than working around them. yes, but still there is a remaining risk in my opinion. It's not just PostgreSQL that will suffer from weird, unpredictable behavior in a broken environment like that. Giving a Windows example, if you're running in a Windows Domain configuration, if the client time drifts too far from the server you can get "The system cannot log you on due to the following error: There is a time difference between the Client and Server." when trying to login. If we add a new server to the cluster, the application will check times as it is in oyur Windows-example, but if it is allready in and working, then it cannot simply shutdown in case of time-diffs. Greg, thanks for your sophisticated hints. But the thread is going a little off-topic now, I guess :) The issue with the time-dependency of WAL archiving and deletion issolved for me by using a global infinite counter to rely on by now. I am sure next questions will come before long and I look forward to read any hints then, if you and others have time to read them. Regards Johannes ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_xlog - files are guaranteed to be sequentialy named?
Frank Wittig wrote: 24 Hex digits means 24^16 unique file names. Assuming your server saves a WAL file each second (you should review your config it it does) it takes (24^16)/(60*60*24*365)=3.84214066×10^14 years to reach the upper bound. (..) It has to be 16^24. But pg does forge filenames other that that. It uses 2 hex digits to count segments. After 256 segments counting starts over and the serial is increased by one. The first 8 positions are the time line which I will ignore for my new calculation. So there is an eight hex digits serial for each time line which takes 256 segments. So there are 16^8*256 unique file names. If I assume one WAL file a second this would reach upper bound (for a single time line) after slightly more than 136 years. Please correct me if my assumptions are wrong. But I would say one can rely on serial file names to increase steadily. Thanks for that answer. That was exactly what I could not immediatelly find mentioned in the documentation. If it is guaranteed - and I understood your comments this way - that the naming follows a sequential order, then I agree with you, that this is enough for a long time. I was not sure wether or not the naming follows this rule. Of course I calculated the number of possible filenames before, but as I said, I was not sure, that Postgresql follows a guaranteed naming convention of always increasing WAL filenames. Anyway, this is now for sure and I will rely on that now. Regards Johannes ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Force ARE in regexp string
Hi! I'm trying to do an advanced regexp match but postgres doesn't seem to let me. I've set regex_flavor to ARE, and I've tried prefixing my strings, i.e. ~* E'***:abc' but for some reason postgres treats all my regexps as BRE's. Common newbie gotchas? I'm trying it directly from psql.exe btw, running postgres 8.4 on a professionally set up Linux machine, and I've also tried it locally on a Windows Bitnami machine with the same results. Thank /J -- 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] Force ARE in regexp string
On 2010-09-15 15:33, Tom Lane wrote: =?ISO-8859-1?Q?Johannes_=D6berg?= writes: I've set regex_flavor to ARE, and I've tried prefixing my strings, i.e. ~* E'***:abc' but for some reason postgres treats all my regexps as BRE's. Well, the symptom as described seems pretty improbable. You didn't show an exact example, but I'm suspecting the real problem is that you're not allowing for backslashes in a string literal getting eaten by string parsing. Do the cases that don't work for you involve backslashes in the regex? regards, tom lane This was indeed what was happening, problem solved, thanks alot! Now, I'm having new problems with Postgres seemingly thinking I'm regexping too much for a single query, but that's will be another thread. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 8.1.4 sanity_check failed on SuSE 8.2
Hello, while the regression tests, there is one that fails: *** ./expected/sanity_check.out Thu Sep 8 20:07:42 2005 --- ./results/sanity_check.out Tue Sep 5 10:27:53 2006 *** *** 17,22 --- 17,24 circle_tbl | t fast_emp4000| t func_index_heap | t + gcircle_tbl | t + gpolygon_tbl| t hash_f8_heap| t hash_i4_heap| t hash_name_heap | t I have found out, that someone else had this problem, but I have found no solution: http://archives.postgresql.org/pgsql-general/2006-05/msg01003.php On the compiling machine, I have (a clean) SuSE 8.2 running, the filesystem is a xfs file system. The last version, Postgres 8.1.3, compiled nicely some time ago. Any ideas how I can fix this? Best regards, Johannes Weberhofer -- |- | weberhofer GmbH | Johannes Weberhofer | information technologies, Austria | | phone : +43 (0)1 5454421 0| email: [EMAIL PROTECTED] | fax : +43 (0)1 5454421 19 | web : http://weberhofer.at | mobile: +43 (0)699 11998315 |--->> ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgres 8.1.4 sanity_check failed on SuSE 8.2
Tom, thank you! from cvs I copied the latest "strategy" to make the sanity checks and removed those values which were added after version 8.1.4. Using that patch, I can compile postgres without any problems. This version runs nicely on several servers. Best regards, Johannes Johannes Weberhofer, Weberhofer GmbH schrieb: I have tried it several times, but no success. I didn't have this issue with older postgres versions on the same machine/system. Best regards, Johannes Weberhofer Tom Lane schrieb: "Johannes Weberhofer, Weberhofer GmbH" <[EMAIL PROTECTED]> writes: while the regression tests, there is one that fails: Repeatably, or did you only see this once? There's a known timing issue that explains this, but it's only been seen once or twice that I know of. 2006-08-06 00:35 tgl * src/test/regress/: expected/sanity_check.out, sql/sanity_check.sql: Tweak sanity_check regression test to display more tables (viz, those without indexes) but not to display temp tables. It's a bit hard to credit that sanity_check could get through a database-wide VACUUM while the preceding create_index test is still trying to clean up its temp tables ... but I see no other explanation for the current failure report from buildfarm member sponge. The report alluded to is http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=sponge&dt=2006-08-05%2021:30:02 regards, tom lane -- |----- | weberhofer GmbH | Johannes Weberhofer | information technologies, Austria | | phone : +43 (0)1 5454421 0| email: [EMAIL PROTECTED] | fax : +43 (0)1 5454421 19 | web : http://weberhofer.at | mobile: +43 (0)699 11998315 |--->> --- src/test/regress/sql/sanity_check.sql 2003-05-28 18:04:02.0 +0200 +++ src/test/regress/sql/sanity_check.sql 2006-09-18 10:11:01.0 +0200 @@ -4,10 +4,13 @@ -- sanity check, if we don't have indices the test will take years to -- complete. But skip TOAST relations since they will have varying -- names depending on the current OID counter. +-- complete. But skip TOAST relations (since they will have varying +-- names depending on the current OID counter) as well as temp tables +-- of other backends (to avoid timing-dependent behavior). -- SELECT relname, relhasindex - FROM pg_class - WHERE relhasindex AND relkind != 't' + FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = relnamespace + WHERE relkind = 'r' AND (nspname ~ '^pg_temp_') IS NOT TRUE ORDER BY relname; -- --- src/test/regress/expected/sanity_check.out 2005-09-08 22:07:42.0 +0200 +++ src/test/regress/expected/sanity_check.out 2006-09-18 11:09:47.0 +0200 @@ -3,72 +3,142 @@ -- sanity check, if we don't have indices the test will take years to -- complete. But skip TOAST relations since they will have varying -- names depending on the current OID counter. +-- complete. But skip TOAST relations (since they will have varying +-- names depending on the current OID counter) as well as temp tables +-- of other backends (to avoid timing-dependent behavior). -- SELECT relname, relhasindex - FROM pg_class - WHERE relhasindex AND relkind != 't' + FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = relnamespace + WHERE relkind = 'r' AND (nspname ~ '^pg_temp_') IS NOT TRUE ORDER BY relname; - relname | relhasindex --+- - bt_f8_heap | t - bt_i4_heap | t - bt_name_heap| t - bt_txt_heap | t - circle_tbl | t - fast_emp4000| t - func_index_heap | t - hash_f8_heap| t - hash_i4_heap| t - hash_name_heap | t - hash_txt_heap | t - ihighway| t - num_exp_add | t - num_exp_div | t - num_exp_ln | t - num_exp_log10 | t - num_exp_mul | t - num_exp_power_10_ln | t - num_exp_sqrt| t - num_exp_sub | t - onek| t - onek2 | t - pg_aggregate| t - pg_am | t - pg_amop | t - pg_amproc | t - pg_attrdef | t - pg_attribute| t - pg_auth_members | t - pg_authid | t - pg_autovacuum | t - pg_cast | t - pg_class| t - pg_constraint | t - pg_conversion | t - pg_database | t - pg_depend | t - pg_description | t - pg_index| t - pg_inherits | t - pg_language | t - pg_largeobject | t - pg_namespace| t - pg_opclass | t - pg_operator | t - pg_pltemplate | t - pg_proc | t - pg_rewrite | t - pg_shdepend | t - pg_statistic| t - pg_tablespace | t - pg_trigge
