Re: [GENERAL] Running pg_upgrade under Debian
Re: Peter Eisentraut 2015-04-24 <553a3b85.1070...@gmx.net> > On 4/20/15 6:09 PM, Bruce Momjian wrote: > > On Mon, Apr 20, 2015 at 07:06:37PM -0300, Alvaro Herrera wrote: > >> ISTM there's a documentation bug here: in the code, the "dump" method > >> checks for tablespaces and raises an error if they are found, but the > >> "upgrade" method does not check. I think the documentation should state > >> that only the dump method does not support tablespaces. > > > > OK, it would be nice if someone could report that to upstream Debian. I > > am a little confused why pg_dump/pg_restore can't use tablespaces > > though. Years ago we used to not use PG-major-version-specific > > subdirectories in tablespaces, but we added that for pg_upgrade, and I > > am sure they would work fine for pg_dump too. > > https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=772202 Yeah I know, it's on the extended TODO list... Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
Re: [GENERAL] PGDATA / data_directory
Re: Benoit Lobréau 2016-08-31 > Hi, > > My company is using PGDATA to store configuration files and the guc > data_directory to give the path to the instance directory. > > They would use it like this: > > pg_ctl start -D -w > > with this directory setup: > > /CONFDIR => postgresql.conf pg_hba.conf pg_ident.conf > /SYSTEM => All the normal stuff in the postgres instance > directory + recovery.conf recovery.done etc... > > Is it commonly used ? That's the default cluster layout on Debian (and Debian-based distributions). /etc/postgresql/9.5/main/ /var/lib/postgresql/9.5/main/ /var/run/postgresql/9.5-main.pg_stat_tmp/ Christoph -- 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] PGDATA / data_directory
Re: Jehan-Guillaume de Rorthais 2016-09-07 <20160907140816.3e13eaa3@firost> > Indeed. I never noticed data_directory was set in postgresql.conf file... > > But then, why starting PostgreSQL with the following command ? > > /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main \ > -c config_file=/etc/postgresql/9.4/main/postgresql.conf > > It seems the following one work as expected and seems more logical with this > setup: > > /usr/lib/postgresql/9.4/bin/postgres -D /etc/postgresql/9.4/main/ TBH, I've been wondering about that myself, but never bothered to to anything about it. Digging in the (now git) history, it's been like that from the very beginning in 2005: https://anonscm.debian.org/cgit/pkg-postgresql/postgresql-common.git/commit/?id=9fa563e78366db3b27d680607c202b6fbb00bef2 It got touched a bit when 8.0 support was added, but basically not changed since then. https://anonscm.debian.org/cgit/pkg-postgresql/postgresql-common.git/commit/?id=fecbaad06c3683452228d31c0baffb01ba626a9a I'll see if I can simply remove it. Thanks for the suggestion. Christoph -- 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] could not connect to server
Re: Antonio Silva 2016-10-16 > 2016-10-15 06:15:20 BRT [995-1] FATAL: data directory > "/var/lib/postgresql/9.5/main" has group or world access > 2016-10-15 06:15:20 BRT [995-2] DETAIL: Permissions should be u=rwx (0700). > > The problem with permissions was already indicated to .pgpass Hi, upgrades do not modify data directory permissions. Something or someone must have modified that manually. And given it also happened to your home directory, that might have affected the whole system... Christoph -- 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] Really unique session ID - PID + connection timestamp?
Re: Durumdara 2016-04-09 > In MS the session id is smallint, so it can repeats after server restarts, > but my coll. found a "session creation timestamp". > This is a key which unique. > With this we can check for died sessions and we can clean their records. > > We want create same mechanism. > I know there are adv. locks in PG, but I want to use session id. > > This could be: > pg_backend_pid() > > May pid repeats. > Where I can get timestamp or some other unique data with I can create a > combined primary key? Btw, what you are describing is exactly what %c in log_line_prefix does. Christoph -- 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] Really unique session ID - PID + connection timestamp?
Re: Stephen Frost 2016-04-09 <20160409115712.gt10...@tamriel.snowman.net> > > Btw, what you are describing is exactly what %c in log_line_prefix > > does. > > That's not currently exposed at an SQL level anywhere though, is it? > Perhaps we should add a way to get that. Another thought would be to > essentially expose 'log_line_prefix()' at the SQL level but I'm not sure > that generally makes sense. We could also have an explicit function to > get MyStartTime, similar to pg_backend_pid(). I don't see how log_line_prefix() would make sense, but pg_session_identifier() (= %c) would make sense to have, in the same way that we have pg_backend_pid(), so the current session could be located in the logs. (Not sure if pg_session_identifier shouldn't also be a pg_stat_activity column, but that might be overkill. Maybe SELECT pg_session_identifier(pid) from pg_stat_activity; ?) MyStartTime is probably too specific. Christoph -- 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] Ubuntu/Debian PGDP
Re: Peter Eisentraut 2016-05-07 <6f86345a-0658-2cd9-27d9-c381846eb...@2ndquadrant.com> > On 5/7/16 2:43 AM, Vincenzo Romano wrote: > > In a fresh new install of PostgreSQL 9.5.2 on Ubuntu 16.04 I am getting > > this: > > > > ... > > Setting up postgresql-9.5 (9.5.2-1.pgdg16.04+1) ... > > Unescaped left brace in regex is deprecated, passed through in regex; > > marked by <-- HERE in m/(? > /usr/sbin/pam_getenv line 78. > > This issue is known and being worked on. It's only a deprecation warning, > so you can ignore it for now. Furthermore, it's not even a bug in PostgreSQL, it's a warning from pam_getenv which is used in the setup scripts. Christoph -- 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] Alternative to psql -c ?
Re: James Le Cuirot 2014-06-25 <20140625144325.49d1124d@red.yakaraplc.local> > Hello, > > I've been using the Chef database cookbook and found it > frustrating because it doesn't allow you to use peer > authentication. The client process generally runs as root and > connects to PostgreSQL using the Ruby pg gem. > > I have patched it to shell out to psql instead. This has the No idea on the "psql -c" part, but I'd try using the pg gem, and use a .pgpass file along with md5 authentication. If you set the PGHOST env var (or PGUSER, PGSERVICE, ...), you don't even need to change any code for that to work with the usual PG default configs. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- 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] Best filesystem for a high load db
Re: Bill Moran 2014-11-25 <20141125111630.d05d58a9eb083c7cf80ed...@potentialtech.com> > Anything with a journal is a performance problem. PostgreSQL effectivly > does its own journalling with the WAL logs. That's not to say that there's > no value to crash recovery to having a journalling filesystem, but it's > just to say that our experience showed journaling filesystems to be slower. > That rules out ext4, unless you disable the journal. I seem to remember > ext4 with journalling disabled being one of the faster filesystems, but I > could be remembering wrong. If you are using a non-journalling FS, you'll be waiting for a full fsck after a system crash. Not sure that's an improvement. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- 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] postgresql command line exploit found in the wild
Re: Daniel Verite 2013-04-08 > Merlin Moncure wrote: > > > if you have an internet facing database, patch it immediately! > > By the way: > > People running 9.1 on debian stable (squeeze) typically use this package: > http://packages.debian.org/squeeze-backports/postgresql-9.1 > > Currently, it looks like the fix is only available in pre-compiled form for > the amd64 architecture (see the bottom of the page). All other archs > including the popular i386 are stuck at version: 9.1.7-1~bpo60+1 This is just packages.debian.org lagging behind. The packages were available on Thursday. (Excluding i386/armel.) Look at the timestamps on http://backports.debian.org/debian-backports/pool/main/p/postgresql-9.1/ . > I find it problematic. One can always switch to the new apt.postgresql.org > repository that has the latest versions, but how many people are going to not > even notice the problem, trusting their normal upgrade path? I'm poking the backports people to throw more resources on building packages there. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- 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 are there no inequality scans for ctid?
Re: hubert depesz lubaczewski 2013-06-28 <20130628085246.ga25...@depesz.com> > On Fri, Jun 28, 2013 at 02:21:10PM +0530, Atri Sharma wrote: > > How would this be helpful for general use cases? Querying on tids on a > > specific page doesn't seem too useful for any other case than the one > > you mentioned above, and IMHO it seems to be the job of vacuum. > > I may be missing something here though. > > Vacuum doesn't move rows around (as far as I can tell by running vacuum > ~ 100 times on bloated table). > > And as for general case - sure. It's not really useful aside from bloat > removal, but I think that bloat removal is important enough to warrant > some help from Pg. It would also be useful for querying broken tables where you "SELECT * FROM badtable WHERE ctid < '(123,0)';" to avoid dying on a bad block. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- 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] A change in the Debian install
Re: Magnus Hagander 2017-04-06 > On Thu, Apr 6, 2017 at 3:46 PM, Stephen Frost wrote: > > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > > (But ... these statements are based on an assumption of out-of-the- > > > box Postgres behavior. I would not exactly put it past the Debian > > > packagers to have decided to change this for reasons of their own, > > > and their track record of telling us about such decisions is many > > > miles south of abysmal. So you might look at whatever patches > > > are in the Debian package to see if there's anything touching > > > pgstat.c's socket-setup logic.) > > > > I don't believe this is really a fair assessment. Maybe at some point > > in the distant past, but not today. Christoph is regularly on this list > > contributing to threads regarding packaging, submitting patches of his > > own for improvements to PG, and the patches currently included in the > > Debian distribution, at least mostly, are for things which really should > > be possible to do with configure options, but which we don't provide > > today, or things we should just be handling already. > > > > +1. While this may have been true in a *very* distant past, it's certainly > not anymore. So let's try to avoid spreading disinformation about that. > > And FWIW, the RPM distributions have about the same number of patches... Thanks Stephen and Magnus. I don't think the way Martin and I have been handling the Debian packages over the last year deserves being bashed that loudly. At least I would expect a Cc on such matters, Tom should know very well whom to address here. > > 51-default-sockets-in-var.patch > > Use /var/run/postgresql/ for the DEFAULT_PGSOCKET_DIR. We really > > should allow this to be changed in configure. > > This looks exactly like something the RPMs want as well, so we should > definitely look at providing that upstream. That one is touching src/include/pg_config_manual.h only, i.e. something that is actually meant to be altered. https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/patches/51-default-sockets-in-var.patch?h=10 > I'll start a discussion with Christoph on if we might, already, be able > > to remove some of these, and where we might be able to make upstream > > changes to remove the need for others. > > That'd be useful. I think you should also include Devrim to figure out what > things would actually make *both* sides happier. Aye. Re: Stephen Frost 2017-04-06 <20170406134629.gt9...@tamriel.snowman.net> > The non-comment/documentation patches include for the Debian PG 9.6 > packages are: > > 50-per-version-dirs.patch > Use version specific installation directories so that several major > versions can be installed in parallel. This includes changing > pkglibdir and includedir_server. Those might be able to be set > through existing configure flags and that's probably something we > could work with Christoph to do. Nod. If someone figures how to translate that to configure (or possibly make) arguments, I'd be happy to move to using that. > There's also a change to pg_config > which might be a bit more difficult to handle in upstream (related to > how pg_config ends up in /usr/bin, but that isn't the "right" BINDIR). pg_config is special there, because we ship it twice, once in /usr/bin/ libpq-dev, and then again in /usr/lib/postgresql/$version/bin/ from postgresql-server-dev-version. Not sure if there's a saner approach that still allows co-installation of multiple versions, while still supplying a pg_config from libpq-dev that allows using --includedir and other version-independent queries. https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/patches/50-per-version-dirs.patch?h=10 > 54-debian-alternatives-for-external-tools.patch > Use 'sensible-editor' for DEFAULT_EDITOR, and 'pager' for > DEFAULT_PAGER. These could also be done through configure switches, I > would think. https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/patches/54-debian-alternatives-for-external-tools.patch?h=10 > 64-pg_upgrade-sockdir > This is a bit of a curious one, the description is: > Fix for: connection to database failed: Unix-domain socket path > > "/build/buildd-postgresql-9.3_9.3~beta1-1-i386-mHjRUH/postgresql-9.3-9.3~beta1/build/contrib/pg_upgrade/.s.PGSQL.50432" > is too long (maximum 107 bytes) > > See also: http://lists.debian.org/debian-wb-team/2013/05/msg00015.html > > This basically adds a mechanism to fall back to using /tmp if the > socket path is too long. Would probably be good to figure out a > better way. That one is actually on my TODO list: https://www.postgresql.org/message-id/20140711094009.GB3115%40msg.df7cb.de I'll need to restart working on it. https://anonscm.debian.org/cgit/pkg-postgresql/postgresql.git/tree/debian/patches/64-pg_upgrade-sockdir?h=10 > filter-debug-prefix-map > Description: Remove -fdebug-prefix-map=/p/w/d=. from CFLA
Re: [GENERAL] Where is pg_hba.conf
Re: Igor Korot 2017-08-13 > I need to modify the pg_hba.conf file to get access to the DB > remotely. > > However, I can't find this file anywhere on the system. Try "SHOW hba_file;". Christoph -- 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] Where is pg_hba.conf
Re: Igor Korot 2017-08-13 > draft=# SHOW hba_file > draft-# SHOW hba_file; > ERROR: syntax error at or near "SHOW" > LINE 2: SHOW hba_file; > ^ Standard beginners error. If you forgot the ";" on the first line, it'll process both lines as a single, erroneous command. Note the "-#" prompt. If you run into that situation again, hit ^C. Christoph -- 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] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100
Re: Harry Ambrose 2017-08-25 > Hi All, > > Sorry to open this can of worms again. However, we are still struggling > with this issue across quite a large amount of our estate. Hi, we've just seen exactly this error on a customer database running 9.5.3 (postgresql95.x86_64 9.5.3-2PGDG.rhel6). Luckily just one tuple was affected. Symptoms were: # select text from k... where id = 719764749; ERROR: unexpected chunk number 0 (expected 1) for toast value 3347468184 in pg_toast_922511637 The toast table itself was perfectly ok, with a single chunk: # select * from pg_toast.pg_toast_922511637 where chunk_id = 3347468184; chunk_id | 3347468184 chunk_seq | 0 chunk_data | ...valid text string... (with bytea_output = 'escape') Updating or deleting the field/row didn't work: # update k... set text = '...same text as above...' where id = 719764749; ERROR: XX000: tuple concurrently updated ORT: simple_heap_delete, heapam.c:3171 # delete from k... where id = 719764749; ERROR: XX000: tuple concurrently updated ORT: simple_heap_delete, heapam.c:3171 The problem persisted over the last two weeks (eventually noticed by pg_dump starting to fail between August 15th and 18th). The server was started on July 26th. Besides the ERRORing statements above, I didn't actively resolve it, suddenly SELECTing the original row just worked again. According to pg_stat_user_tables, autovacuum didn't hit in. I can't say if there were backends open for two weeks. At the time it resolved itself, the oldest backend was from August 27th. If xmin/xmax/multixact... data from this server is interesting, I can extract it on request. Christoph -- 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] Postgres 10.1 fails to start: server did not start in time
Re: Tom Lane 2017-11-10 <8027.1510347...@sss.pgh.pa.us> > > The recovery succeeds, but when I go to start the cluster on the > > standby, it begins to replay the WAL, and does so for about 30 > > seconds. Then I get a line in my log saying: > > >> pg_ctl: server did not start in time Hi Adam, how did you start the server? Via pg_ctlcluster, the init system, or directly via pg_ctl? > > Followed by: > >> 2017-11-10 20:27:35.907 UTC [7132] LOG: received smart shutdown request > >> ERROR [063]: : terminated on signal [SIGTERM] > > ... pg_ctl itself wouldn't decide to forcibly shut down the server > if the timeout expired. It merely stops waiting and tells you so. > It seems like this must represent misdesign of whatever start script > you're using. I think you need to complain to the Debian packagers > about that. pg_ctlcluster doesn't shut down if startup fails, but to be sure, we'd need to see the full log of whatever initiated the startup. If you are using systemd, what does `systemctl status postgresql@10-main` report? If that doesn't have anything, also check journalctl. Christoph -- 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 on Debian servers
Re: Magnus Hagander 2017-11-11 > > Is there any way that either the package maintainer or a site > > administrator/programmer such as myself can mark the Postgres server > > packages as "manual upgrade only" or similar? Or since I'm almost certainly > > not the first person to be bitten by this, is there a preferred hack in > > mitigation? > > > Certainly. Unrelated to PostgreSQL, this is a standard feature in Debian. > Commonly used to prevent things like kernel upgrades from happening on the > same schedule as others. > > Basically, you put the package "on hold". See the debian administratino > guide at > https://debian-administration.org/article/67/Preventing_Debian_Package_Upgrades Another thing you can do is preventing package upgrades from stopping/starting services by using a policy-rc.d: https://jpetazzo.github.io/2013/10/06/policy-rc-d-do-not-start-services-automatically/ https://people.debian.org/~hmh/invokerc.d-policyrc.d-specification.txt However, if you do that, you need to take measures to actually restart into the new version manually later. Christoph -- 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] Postgres 10.1 fails to start: server did not start in time
Re: Adam Brusselback 2017-11-11 > Hey Christoph, I tried starting it with init (service postgresql > start), and pg_ctlcluster. > > I modified the pg_ctl.conf and set the timeout higher so I could just > get my cluster back up and running properly, so I can't give you the > info on what systemctl status says at the moment. It looks like it's interference from systemd here. The problem is easily reproduced by putting '-t 0' into pg_ctl.conf: ● postgresql@10-main.service - PostgreSQL Cluster 10-main Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled; vendor preset: enabled) Active: failed (Result: exit-code) since Sat 2017-11-11 21:10:56 CET; 9ms ago Process: 17946 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast 10-main stop (code=exited, status=1/FAILURE) Process: 18000 ExecStart=postgresql@10-main --skip-systemctl-redirect 10-main start (code=exited, status=1/FAILURE) Main PID: 17878 (code=exited, status=0/SUCCESS) Nov 11 21:10:55 lehmann systemd[1]: Starting PostgreSQL Cluster 10-main... Nov 11 21:10:56 lehmann postgresql@10-main[18000]: Error: /usr/lib/postgresql/10/bin/pg_ctl /usr/lib/postgresql/10/bin/pg_ctl start -D /var/lib/postgresql/10/main -l /var/log/postgresql/postgresql-10-main.log -t 0 -s -o -c config_file="/etc/postgresql/10/main/postgresql.conf" exited with status 1: Nov 11 21:10:56 lehmann postgresql@10-main[18000]: pg_ctl: server did not start in time Nov 11 21:10:56 lehmann systemd[1]: postgresql@10-main.service: Control process exited, code=exited status=1 Nov 11 21:10:56 lehmann systemd[1]: Failed to start PostgreSQL Cluster 10-main. Nov 11 21:10:56 lehmann systemd[1]: postgresql@10-main.service: Unit entered failed state. Nov 11 21:10:56 lehmann systemd[1]: postgresql@10-main.service: Failed with result 'exit-code'. In other words, systemd will by default stop a service that fails to start. I'm investigating if it's a good idea to tell systemd to ignore the exit code of pg_ctl(cluster). Possibly moving to Type=notify is the best solution, but not all majors support that yet. Will report back once I have a solution. Christoph -- 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] Postgres 10.1 fails to start: server did not start in time
Re: To Adam Brusselback 2017-11-11 <2017205316.u56lkmkakdmcx...@msg.df7cb.de> > I'm investigating if it's a good idea to tell systemd to ignore the > exit code of pg_ctl(cluster). Telling systemd to ignore ExecStart errors seems to be the correct solution. The service will still be active, with the startup error being shown: ● postgresql@10-main.service - PostgreSQL Cluster 10-main Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled; vendor preset: enabled) Active: active (running) since Sun 2017-11-12 13:24:21 CET; 210ms ago Process: 31892 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast 10-main stop (code=exited, status=0/SUCCESS) Process: 31922 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 10-main start (code=exited, status=1/FAILURE) Main PID: 31928 (postgres) Tasks: 8 (limit: 4915) CGroup: /system.slice/system-postgresql.slice/postgresql@10-main.service ├─31928 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf ├─31931 postgres: 10/main: checkpointer process ├─31932 postgres: 10/main: writer process ├─31933 postgres: 10/main: wal writer process ├─31934 postgres: 10/main: autovacuum launcher process ├─31935 postgres: 10/main: archiver process ├─31936 postgres: 10/main: stats collector process └─31937 postgres: 10/main: bgworker: logical replication launcher Nov 12 13:24:20 lehmann systemd[1]: Starting PostgreSQL Cluster 10-main... Nov 12 13:24:21 lehmann postgresql@10-main[31922]: Error: /usr/lib/postgresql/10/bin/pg_ctl /usr/lib/postgresql/10/bin/pg_ctl start -D /var/lib/postgresql/10/main -l /var/log/postgresql/postgresql-10-main.log -t 0 -s -o -c config_file="/etc/postgresql/10/main/postgresql.conf" exited with status 1: Nov 12 13:24:21 lehmann postgresql@10-main[31922]: pg_ctl: server did not start in time Nov 12 13:24:21 lehmann systemd[1]: postgresql@10-main.service: PID file /var/run/postgresql/10-main.pid not readable (yet?) after start: No such file or directory Nov 12 13:24:21 lehmann systemd[1]: Started PostgreSQL Cluster 10-main. Fixed in https://anonscm.debian.org/cgit/pkg-postgresql/postgresql-common.git/commit/?id=fc57e655c71e8f6bcb3010b054f5adbf32a224d7 , thanks for the report! Christoph -- 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] Postgres 10.1 fails to start: server did not start in time
Re: Peter J. Holzer 2017-11-12 <20171112173559.m6chmbyf4vz6f...@hjp.at> > Wouldn't it be better to remove the timeout? If some other service > depends on PostgreSQL it probably shouldn't be startet until PostgreSQL > is really up and services which don't need PostgreSQL (e.g. SSH or X11 > login or a web- or mail server) shouldn't depend on it. > > One of the purported advantages of systemd over SystemV init is that it > starts up services in parallel, so a service which takes a long (or > infinite) time to start doesn't block other services. If you don't want to block, don't depend on the database service. That question is independent from the timeout. Christoph -- 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] Postgres 10.1 fails to start: server did not start in time
Re: Tom Lane 2017-11-12 <20802.1510513...@sss.pgh.pa.us> > Agreed, but I think Peter has a point: why is there a timeout at all, > let alone one as short as 30 seconds? Since systemd doesn't serialize > service starts unnecessarily, there seems little value in giving up > quickly. And we know that cases such as crash recovery may take more > than that. The default systemd timeout seems to be 90s. I have already changed the systemd timeout to infinity (start) and 1h (stop), so only the default pg_ctl timeout remains (60s), which I'd rather not override unilaterally. https://anonscm.debian.org/cgit/pkg-postgresql/postgresql-common.git/tree/systemd/postgresql@.service#n18 That said, isn't 60s way too small for shutting down larger clusters? And likewise for starting? Christoph -- 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] Query runs forever after upgrading to 9.3
Re: Andrus 2013-09-23 > SELECT * FROM toode > WHERE toode in (SELECT toode FROM tempkaive) > OR toode in (SELECT toode FROM tempalgsemu) Generally, WHERE IN (SELECT) should be rewritten as WHERE EXISTS (SELECT): SELECT * FROM toode o WHERE EXISTS (SELECT toode FROM tempkaive i WHERE o.toode = i.toode) OR EXISTS (SELECT toode FROM tempalgsemu i WHERE o.toode = i.toode) Also, ANALYZEing the tables after the upgrade might help if this has not yet been done. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- 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] Moving some of Postgres off a SSD
Re: Robert James 2012-12-18 > I have Postgres running on a SSD. The data is now almost 50GB, which > is filling up the drive. > > How can I move some of the data to my HDD? Use a TABLESPACE. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
