Hello,
I'm newly exposed to a Postgres 9.4 database system, and am
trying to understand how I might optimize a query that is taking
a long time to return.
What I'm observing is an uncached query that takes much much
longer to complete, sometimes minutes longer, when
enable_bitmapscan is true. Ev
On Thu, Nov 1, 2018 at 4:25 AM Jeff Janes wrote:
> On Wed, Oct 31, 2018 at 1:38 AM Torsten Förtsch
> wrote:
>> I am working on restoring a database from a base backup + WAL. With the
>> default settings the database replays about 3-4 WAL files per second. The
>> startup process takes about 65%
On Wed, 31 Oct 2018, Adrian Klaver wrote:
Even with assurances I would back up that directory(assuming space available)
before proceeding with a rebuild. Or do you have a recent dump of the
cluster?
Adrian,
That's my thinking, too. No, an explicit pg_dumpall is too old to be
useful. I have
On Wed, 31 Oct 2018, Adrian Klaver wrote:
Hmm in the build script the difference is:
VERSION=${VERSION:-10.3}
PG_VERSION=${PG_VERSION:-10.3}
--docdir=/usr/doc/$PRGNAM-$VERSION \
--datadir=/usr/share/$PRGNAM-$PG_VERSION \
Wonder where the script is finding PG_VERSION?
Do you have env variable
On 10/31/18 3:19 PM, Rich Shepard wrote:
On Wed, 31 Oct 2018, Adrian Klaver wrote:
Well there is something strange going. From a previous post:
Andrew,
Yet it ran without a whimper from the upgrade last March 1st to this
morning when I modified postgresql.conf.
It's the middle of the
On 10/31/18 3:19 PM, Rich Shepard wrote:
On Wed, 31 Oct 2018, Adrian Klaver wrote:
Well there is something strange going. From a previous post:
Andrew,
Yet it ran without a whimper from the upgrade last March 1st to this
morning when I modified postgresql.conf.
It's the middle of the
On Wed, 31 Oct 2018, Adrian Klaver wrote:
Well there is something strange going. From a previous post:
Andrew,
Yet it ran without a whimper from the upgrade last March 1st to this
morning when I modified postgresql.conf.
It's the middle of the night in central Europe so I expect to hear
On 10/31/18 3:08 PM, Adrian Klaver wrote:
On 10/31/18 3:03 PM, Rich Shepard wrote:
On Wed, 31 Oct 2018, Andrew Gierth wrote:
What this says is that you somehow have a pg 10.3 binary which has been
compiled with ./configure --datadir=/usr/share/postgresql-10.2
which seems, to say the least, so
On 10/31/18 3:03 PM, Rich Shepard wrote:
On Wed, 31 Oct 2018, Andrew Gierth wrote:
What this says is that you somehow have a pg 10.3 binary which has been
compiled with ./configure --datadir=/usr/share/postgresql-10.2
which seems, to say the least, somewhat odd.
Andrew,
Quite odd rather
On 10/31/18 2:40 PM, Rich Shepard wrote:
On Wed, 31 Oct 2018, Rich Shepard wrote:
Still bad links remaining.
Every pg_* not in /usr/lib/postgresql/10.3/bin/ now points to its
namesake
there.
Hmm. Grasping at straws. In a previous post you mentioned:
"If it matters, there's no /etc/pos
On Wed, 31 Oct 2018, Andrew Gierth wrote:
What this says is that you somehow have a pg 10.3 binary which has been
compiled with ./configure --datadir=/usr/share/postgresql-10.2
which seems, to say the least, somewhat odd.
Andrew,
Quite odd rather than somewhat odd because the configure opt
On Wed, 31 Oct 2018, Adrian Klaver wrote:
So no, as I presume you rebooted on the kernel upgrade which caused the
Postgres server to stop/start.
True. It stopped for the time it took the server to reboot.
Rich
On 10/31/18 2:01 PM, Rich Shepard wrote:
Did the server been running continuously from the upgrade to the time you
made the listen_addresses change?
Yes, other than a few kernel upgrades.
So no, as I presume you rebooted on the kernel upgrade which caused the
Postgres server to stop/star
> "Rich" == Rich Shepard writes:
Rich> Yes, pg_config is present but pointing to the wrong directory:
Rich> # /usr/lib/postgresql/10.3/bin/pg_config --sharedir
Rich> /usr/share/postgresql-10.2
What this says is that you somehow have a pg 10.3 binary which has been
compiled with ./config
On Wed, 31 Oct 2018, Rich Shepard wrote:
Still bad links remaining.
Every pg_* not in /usr/lib/postgresql/10.3/bin/ now points to its namesake
there.
Question: if pg_dump, pg_dumpall, pg_restore, pg_ctl, and pg_controldata
have symlinks in /usr/bin/ do they also need symlinks in /bin/? Th
On Wed, 31 Oct 2018, Rich Shepard wrote:
I'll fix those links and report the results of running pg_ctl start.
Still bad links remaining. Some of those symlinks in /usr/bin/ dated back
to versons 9.4 and 9.6. Why they were not removed during upgrades remains a
mystery.
Rich
On Wed, 31 Oct 2018, Rich Shepard wrote:
[1] This prompted me to look for more pg_config files, and I found a symlink
in /usr/bin/ that pointed to /usr/lib/postgresql/10.2/bin/pg_config which
does not exist. I changed that symlink to point to the 10.3/ pg_config
version but there's still a broke
On Wed, 31 Oct 2018, Adrian Klaver wrote:
Are there actually 10.2/ directories or is that just what you are seeing in
the error messages and the pg_config output?
Adrian,
No 10.2/ directories, only what is shown in the error messages and
pg_config output.
Previously you used:
/usr/lib/pos
On 10/31/18 1:09 PM, Rich Shepard wrote:
On Wed, 31 Oct 2018, Adrian Klaver wrote:
What does:
pg_ctl --version
show?
# pg_ctl --version
pg_ctl (PostgreSQL) 10.3
So when you added the new application did you make any other changes?
I did not add another application; grass has been insta
On Wed, 31 Oct 2018, Adrian Klaver wrote:
What does:
pg_ctl --version
show?
# pg_ctl --version
pg_ctl (PostgreSQL) 10.3
So when you added the new application did you make any other changes?
I did not add another application; grass has been installed here for
decades. Because I could not
On 10/31/18 12:14 PM, Rich Shepard wrote:
On Wed, 31 Oct 2018, Adrian Klaver wrote:
listen_addresses = ''
Adrian,
#listen_addresses = ''
$ pg_ctl start -D /var/lib/pgsql/10.3/data/
waiting for server to start2018-10-31 12:12:39.530 PDT [4398]
FATAL: could not open directory
"/usr/sha
On Wed, 31 Oct 2018, Adrian Klaver wrote:
What does:
/usr/lib/postgresql/10.3/bin/pg_config --version
show?
# /usr/lib/postgresql/10.3/bin/pg_config --version
PostgreSQL 10.3
What does:
ps ax | grep post
show?
# ps ax | grep post
1307 ?Ss 1:29 /usr/libexec/postfix/master -
On Wed, 31 Oct 2018, Adrian Klaver wrote:
listen_addresses = ''
Adrian,
#listen_addresses = ''
$ pg_ctl start -D /var/lib/pgsql/10.3/data/
waiting for server to start2018-10-31 12:12:39.530 PDT [4398] FATAL: could not open
directory "/usr/share/postgresql-10.2/timezonesets": No such fi
On 10/31/18 11:48 AM, Rich Shepard wrote:
On Wed, 31 Oct 2018, Andrew Gierth wrote:
Is there a pg_config binary in /usr/lib/postgresql/10.3/bin/ and if so,
what is the output of /usr/lib/postgresql/10.3/bin/pg_config --sharedir
Andrew,
Yes, pg_config is present but pointing to the wrong d
On 10/31/18 11:33 AM, Rich Shepard wrote:
On Wed, 31 Oct 2018, Adrian Klaver wrote:
If you refuse to implement the suggestions I asked for then I cannot
help you, as you are now off on a different tangent. One that on the
face of it is dangerous.
In var/lib/pgsql/10.3/data/postgresql.conf
On Wed, 31 Oct 2018, Andrew Gierth wrote:
Is there a pg_config binary in /usr/lib/postgresql/10.3/bin/ and if so,
what is the output of /usr/lib/postgresql/10.3/bin/pg_config --sharedir
Andrew,
Yes, pg_config is present but pointing to the wrong directory:
# /usr/lib/postgresql/10.3/bin/pg_
> "Rich" == Rich Shepard writes:
Rich> I managed to mess up postgresql-10.3 on this Slackware-14.2
Rich> desktop server/workstation. It worked OK until I tried adding
Rich> access to an another application.
Rich> waiting for server to start2018-10-31 10:02:01.312 PDT [1285] FATAL:
On Wed, 31 Oct 2018, Adrian Klaver wrote:
If you refuse to implement the suggestions I asked for then I cannot help
you, as you are now off on a different tangent. One that on the face of it is
dangerous.
In var/lib/pgsql/10.3/data/postgresql.conf:
# - Connection Settings -
_addresses = '
On 10/31/18 11:15 AM, Rich Shepard wrote:
On Wed, 31 Oct 2018, Adrian Klaver wrote:
You said it made a difference when you added it, just trying to figure
out if removing it also makes a difference. If not then we need to
look elsewhere for an explanation.
Adrian,
Each time I hit a broke
On Wed, 31 Oct 2018, Adrian Klaver wrote:
You said it made a difference when you added it, just trying to figure out if
removing it also makes a difference. If not then we need to look elsewhere
for an explanation.
Adrian,
Each time I hit a broken symlink pg_ctl told me which link was brok
On 10/31/18 10:55 AM, Rich Shepard wrote:
On Wed, 31 Oct 2018, Adrian Klaver wrote:
What was the listening address you added?
Adrian,
I added the host name.
What happens if you remove the listening address?
I don't think this makes a difference. pg_ctl is calling a program that
loo
On Wed, 31 Oct 2018, Adrian Klaver wrote:
What was the listening address you added?
Adrian,
I added the host name.
What happens if you remove the listening address?
I don't think this makes a difference. pg_ctl is calling a program that
looks for timezonesets in the wrong directory
On 10/31/18 10:18 AM, Rich Shepard wrote:
I managed to mess up postgresql-10.3 on this Slackware-14.2 desktop
server/workstation. It worked OK until I tried adding access to an another
application.
For a reason I don't know, adding that listening address revealed that
many sym links are lo
I managed to mess up postgresql-10.3 on this Slackware-14.2 desktop
server/workstation. It worked OK until I tried adding access to an another
application.
For a reason I don't know, adding that listening address revealed that
many sym links are looking for 10.2 directories. I've found and fi
This tool may be useful:
https://github.com/joyent/pg_prefaulter
Faults pages into PostgreSQL shared_buffers or filesystem caches in advance
of WAL apply
Nicolas
On Wed, Oct 31, 2018 at 6:38 AM Torsten Förtsch
wrote:
> Hi,
>
> I am working on restoring a database from a base backup + WAL. With
Greetings,
* Jeff Janes (jeff.ja...@gmail.com) wrote:
> One way I found to speed up restore_command is to have another program run
> a few WAL files ahead of it, copying the WAL from the real archive into a
> scratch space which is on the same filesystem as pg_xlog/pg_wal. Then have
> restore_com
Greetings,
* Torsten Förtsch (tfoertsch...@gmail.com) wrote:
> I am working on restoring a database from a base backup + WAL. With the
> default settings the database replays about 3-4 WAL files per second. The
> startup process takes about 65% of a CPU and writes data with something
> between 50
On Wed, Oct 31, 2018 at 1:38 AM Torsten Förtsch
wrote:
> Hi,
>
> I am working on restoring a database from a base backup + WAL. With the
> default settings the database replays about 3-4 WAL files per second. The
> startup process takes about 65% of a CPU and writes data with something
> between
I can second this as an option. We’ve done some editing with Libre office as
well in Postgres.
bobb
On Oct 31, 2018, at 5:01 AM, Tony Shelver
mailto:tshel...@gmail.com>> wrote:
For a quick and dirty data editor, LibreOffice Base seems to work fine.
On Tue, 30 Oct 2018 at 23:05, Tim Clarke
Yep, I messed a couple of options, I'm sorry.
regards,
Szymon Lipiński
On Wed, 31 Oct 2018 at 15:07, Adrian Klaver
wrote:
> On 10/31/18 6:58 AM, Szymon Lipiński wrote:
> > This is what I have in postgres 10 pg_settings table: and the
> > documentation has different description. What's more the
On 10/31/18 6:58 AM, Szymon Lipiński wrote:
This is what I have in postgres 10 pg_settings table: and the
documentation has different description. What's more the default value
is outside the range of the min/max value.
Not seeing it:
0 < 0.1 < 100
reltuples is a measure of the sixe of the
On 10/31/18 2:03 AM, GPT wrote:
Very good morning,
Thanks very much for your direct, clear and enlightening response!
As regards Q2, and any other dynamic behaviour/feature or whatever PG
includes or will include in the future and has to do with 3rd entities
(modules, or whatever) of which the
This is what I have in postgres 10 pg_settings table: and the documentation
has different description. What's more the default value is outside the
range of the min/max value.
-[ RECORD 2
]---+--
name
Tim Clarke-2 wrote
> On 30/10/2018 20:32, Martin Mueller wrote:
>>
>> I have used Aqua Data Studio for several years. Jetbrains recently
>> released a similar product. Academic licensing is affordable (~ $200 a
>> year) and very cheap if considered in terms of the time it saves you.
>>
>> *From:
On 10/30/18 9:20 AM, Igor Korot wrote:
Now is there a command to flush the log - delete the content of it?
The only thing I know of is:
https://www.postgresql.org/docs/10/static/functions-admin.html
pg_rotate_logfile() boolean Rotate server's log file
All I'm looking for in th
On Wed, Oct 31, 2018 at 10:49:04AM +0100, Laurenz Albe wrote:
> Nicolas Paris wrote:
> > > > The documentation[1] says thesaurus can include informations of terms
> > > > relationships such broader terms, preferred terms ...
> > > > I haven't been able to find out how to exploit those relationship
Looked like fixed now.
枫 wrote:
> The routine of using pg is first adding unprivileged user group and user,
> starting pg via the new user.
>
> For the safety software and the safety rule of my company.It is not easy to
> add a unprivileged user to operate pg.
>
> Can I have a way to change the restriction of the unp
hi all,
The routine of using pg is first adding unprivileged user group and user,
starting pg via the new user.
For the safety software and the safety rule of my company.It is not easy to add
a unprivileged user to operate pg.
Can I have a way to change the restriction of the unprivileged u
Hi,
Postgres was installed through a repo with yum install.
I will try to run it with another type.
Regards
Marian
> Op 30 okt. 2018 om 21:56 heeft Adrian Klaver het
> volgende geschreven:
>
>> On 10/30/18 9:27 AM, Marian Forums wrote:
>> Hi Adrain,
>> Thanks for your reply.
>
>
> How was
For a quick and dirty data editor, LibreOffice Base seems to work fine.
On Tue, 30 Oct 2018 at 23:05, Tim Clarke
wrote:
> On 30/10/2018 20:32, Martin Mueller wrote:
> >
> > I have used Aqua Data Studio for several years. Jetbrains recently
> > released a similar product. Academic licensing is af
Nicolas Paris wrote:
> > > The documentation[1] says thesaurus can include informations of terms
> > > relationships such broader terms, preferred terms ...
> > > I haven't been able to find out how to exploit those relationship in
> > > postgres. Is there any keyword to and associated syntax to ma
Very good morning,
Thanks very much for your direct, clear and enlightening response!
As regards Q2, and any other dynamic behaviour/feature or whatever PG
includes or will include in the future and has to do with 3rd entities
(modules, or whatever) of which the behaviour is out of the PG
control
On Wed, Oct 31, 2018 at 07:56:28AM +0100, Laurenz Albe wrote:
> > The documentation[1] says thesaurus can include informations of terms
> > relationships such broader terms, preferred terms ...
> > I haven't been able to find out how to exploit those relationship in
> > postgres. Is there any keywo
Igor Korot wrote:
> Now is there a command to flush the log - delete the content of it?
No, managing the logs is outside of PostgreSQL's responsibility.
But it shouldn't be a problem to do this outside the database.
Of course you could write a funtion in PostgreSQL that uses one
of the "untrusted
55 matches
Mail list logo
