Re: [GENERAL] pg_upgrade error on FreeBSD (9.1 -> 9.5)
On Sat, Jan 21, 2017 at 9:53 PM, Tom Lane wrote: > Amitabh Kant writes: > > command: "/var/tmp/pgbin.SPOsRj4D/bin/pg_ctl" -w -l > "pg_upgrade_server.log" > > -D "/usr/local/pgsql/data91" -o "-p 50432 -b -c listen_addresses='' -c > > unix_socket_permissions=0700 -c unix_socket_directory='/usr/ > local/pgsql'" > > Note the unix_socket_directory parameter, which is indeed being applied > because we can see it again in the ps output: > > > pgsql 26636 0.0 1.4 66960 14512 - Is4:08AM 0:00.06 > > /var/tmp/pgbin.SPOsRj4D/bin/postgres -D /usr/local/pgsql/data91 -p > 50432 -b > > -c listen_addresses= -c unix_socket_permissions=0700 -c > > unix_socket_directory=/usr/local/pgsql > > However, your psql is looking for the socket in /tmp: > > > $ psql -p 50432 -d template1 > > psql: could not connect to server: No such file or directory > > Is the server running locally and accepting > > connections on Unix domain socket "/tmp/.s.PGSQL.50432"? > > You could successfully connect to that server with > "psql -p 50432 -h /usr/local/pgsql ...", I expect. > > The question is why pg_upgrade issued that option and then failed to > cope with the consequences. I suspect it has something to do with one > installation being configured with different default socket directory > than the other, but I don't have enough facts. > > regards, tom lane > Yes, it does connect using Unix domain socket as you suggested. PG 9.5 is the stock install as present on FreeBSD. I will have to check the script that installs PG 9.1 in an alternate location for any changes from the default. regards Amitabh
[GENERAL] Partitioned "views"
Hello, we use a materialized view to aggregate datas from a very big table containing logs. The source table is partitioned, one table for a day. Since the refresh of the materialized view seems to grow a lot about timing, we would like to know if it is pssible to make a "partitioned materialized views", in order to update *only* current day logs aggregation, leaving old days untouchable. Thank you! /F
Re: [GENERAL] Partitioned "views"
Greetings, * Job (j...@colliniconsulting.it) wrote: > we use a materialized view to aggregate datas from a very big table > containing logs. > The source table is partitioned, one table for a day. > > Since the refresh of the materialized view seems to grow a lot about timing, > we would like to know if it is pssible to make a "partitioned materialized > views", in order to update *only* current day logs aggregation, leaving old > days untouchable. You can do this, but you have to write the SQL for it yourself, there's no current way in PG to ask for a materialized view to be partitioned. The mat view takes longer and longer to update because it runs the full query. What you really want to do is have a side-table that you update regularly with appropriate SQL to issue UPDATE statements for just the current day (or whatever). Another approach which can be used is to have a trigger which will automatically update the side-table for every change to the 'big' table, but that will mean every update on the 'big' table takes longer and if the updates are happening concurrently then you may run into locking, and possibly deadlocking if it's done wrong. Thanks! Stephen signature.asc Description: Digital signature
R: [GENERAL] Partitioned "views"
Hi Stephen. >>The mat view takes longer and longer to update because it runs the full >>query. What you really want to do is have a side-table that you update >>regularly with appropriate SQL to issue UPDATE statements for just the >>current day (or whatever). If correct, i leave only last datas into "side-table" and syncronize only these datas in the materialized view. If i delete datas from the side-table (ex- truncate) during the next materialized view update they will be lost or remain intact? Thank you Francesco -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Partitioned "views"
Greetings, * Job (j...@colliniconsulting.it) wrote: > >>The mat view takes longer and longer to update because it runs the full > >>query. What you really want to do is have a side-table that you update > >>regularly with appropriate SQL to issue UPDATE statements for just the > >>current day (or whatever). > > If correct, i leave only last datas into "side-table" and syncronize only > these datas in the materialized view. > If i delete datas from the side-table (ex- truncate) during the next > materialized view update they will be lost or remain intact? I was suggesting that you, essentially, write your own SQL to have a materialized view, *not* use the PG materialized view system. In other words, the 'side-table' that you create would be *your* materialized view, but to PG, it'd just look like a regular table. You can't modify a PG materialized view. Thanks! Stephen signature.asc Description: Digital signature
