users can be found
on the wiki here: https://wiki.postgresql.org/wiki/PGLister_Announce
Once the migration of these lists is complete, an 'after' email will be
sent out.
Thanks!
Stephen
signature.asc
Description: Digital signature
ample everything that has to
> do with the operating system (location of configuration file
> or socket directories).
This isn't quite correct any longer- with PG10, we have a default role
called 'pg_read_all_settings' which can be GRANT'd to other roles to
allow viewing o
ll include
the GRANT statement, which isn't really correct either.
That's obviously a change from what we had before and wasn't
intentional.
> This is *REALLY BAD*. Quite aside from the restore being wrong,
> those two sequences should never ever give different results.
&
Hi Michael,
So if I'm reading this correctly, the proper way to do my use case is to use
partitions of partitions, right?
--Stephen
-Original Message-
From: Michael Paquier [mailto:michael.paqu...@gmail.com]
Sent: Tuesday, October 31, 2017 4:06 PM
To: Stephen Froehlich
Cc:
quot; lotsa_data_20171027_src1" would overlap partition
"lotsa_data_20171027_src3"
Why am I getting this error? (Also, if I go "FROM ('2017-10-26 00:00:00 UTC')
TO ('2017-10-27 00:00:00 UTC')" I also get overlap errors.
Thanks for your help ...
--Steph
e GRANT'ing rights on the database, but
there's only a couple such rights (eg: CONNECT) and you might be better
off managing those in another way.
Thanks!
Stephen
signature.asc
Description: Digital signature
ents required for it in other ways (as discussed
elsewhere on this thread).
Thanks!
Stephen
signature.asc
Description: Digital signature
arly when it comes to backups
and recovery.
Thanks!
Stephen
signature.asc
Description: Digital signature
o me is what you're actaully
trying to solve by using such a method..? You haven't said anywhere
what's wrong with archive_command (I know that there certainly are some
things wrong with it, of course, but there are solutions to a number of
those issues that isn't a hack like this ...).
Thanks!
Stephen
signature.asc
Description: Digital signature
checkpoint
2017-10-20 11:31:40.767 MDT [33361] NOTICE: database system is shut down
Thanks in advance for your help ...
--Stephen
Stephen Froehlich
Sr. Strategist, CableLabs(r)
s.froehl...@cablelabs.com
Tel: +1 (303) 661-3708
the way to handle authentication in that
environment (or, well, really, LDAP isn't a terribly secure option in
any environment, but if it's all you've got and you're not allowed to
change then I suppose there's not much to be done about it).
Thanks!
Stephen
signat
x27;t log in
> after creating an account just to get on a mailing list so I can send an
> email.
One of the several reasons I do not like RDS...
I have not been able to figure it out either. Instead I used the
information_schema to generate a bunch of 'ALTER xxx OWNER TO yyy;'
statements, which when run as your current owner user will allow you to
give away your ownership to another user.
After that, make sure to only create objects using the "stepmadmin"
user, or you'll have to jump through hoops yet again.
-- Stephen
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
discussions about having an actual
'read only' role that can be granted out. Now that we've actually got
default roles in PG, this is something that becomes at least somewhat
more practical and might be able to happen for PG11 if there's interest
and effort put into it.
Thank
ecifically
address PG backups and to do things correctly (such as making sure WAL
archiving is working and that WAL files are sync'd to disk before
telling PG that it's been copied).
Thanks!
Stephen
signature.asc
Description: Digital signature
oing a proper PG backup. Use a tool which
has been developed specifically for PG such as pgBackRest, barman,
WAL-E, WAL-G, etc.
Thanks!
Stephen
signature.asc
Description: Digital signature
Greetings John,
* John R Pierce (pie...@hogranch.com) wrote:
> On 9/20/2017 6:55 AM, Stephen Frost wrote:
> >If AD is in the mix here, then there's no need to have things happening
> >at the database level when it comes to passwords- configure PG to use
> >Kerberos an
njob and ldap_fdw, or similar,
people just have to realize that there's a bit of lag. The same goes
for creating accounts in the first place in the database, of course.
Thanks!
Stephen
signature.asc
Description: Digital signature
Bruce,
* Bruce Momjian (br...@momjian.us) wrote:
> On Tue, Sep 19, 2017 at 01:28:11PM -0400, Stephen Frost wrote:
> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > > chiru r writes:
> > > > We are looking for User profiles in ope source PostgreSQL.
> > > >
On 2017-09-19 15:42, Jeff Janes wrote:
> On Tue, Sep 19, 2017 at 12:20 PM, Bruce Momjian <mailto:br...@momjian.us>> wrote:
>
> On Wed, Sep 6, 2017 at 04:19:52PM -0400, Stephen Cook wrote:
> > Hello!
> >
> > Is there a way to decrypt data
al authentication system (Kerberos, for
example) which can deal with this, but I do think this is also something
we should be considering for core, especially now that we've got a
reasonable password-based authentication method with SCRAM.
Thanks!
Stephen
signature.asc
Description: Digital signature
Hello!
Is there a way to decrypt data encrypted with the pgcrypto "encrypt"
function, outside the database? Assuming that I know the key etc...
Thanks!
-- Stephen
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscrip
* Christoph Moench-Tegeder (c...@burggraben.net) wrote:
> ## Stephen Frost (sfr...@snowman.net):
>
> > Worse, such scripts run the serious risk of losing WAL if a crash
> > happens because nothing is ensuring that the WAL has been sync'd to disk
> > before retur
and unsuccessful
archive command runs. I'm pretty sure barman supports back to 8.4 and I
know pgbackrest does.
Thanks!
Stephen
signature.asc
Description: Digital signature
entirely reasonable attack vector to consider and
database-level encryption is one approach which could (if implemented
properly) address that vector. There are certainly other approaches to
address that vector as well, of course, such as using backup technology
which provides its own encryption, though that requires managing a
different set of keys possibly, or run the backup through GPG or similar
but that gets painful quickly, et al.
> Security isn't something you do one time and you're done, it's a
> constant process of design, review, updates, and education.
Agreed.
Thanks!
Stephen
signature.asc
Description: Digital signature
Hello!
When a client gets the error message about "remaining connection slots
are reserved for non-replication superuser connections", is this logged?
What should I be grep-ing for?
Thanks!
-- Stephen
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
g about it until
> we're in beta phase :-(. In the meantime you could perhaps do the
> folding by hand:
Put it into the CF..?
Thanks!
Stephen
signature.asc
Description: Digital signature
re ideal and both are avoided by simply using Kerberos, which is
what AD uses.
Authentication using LDAP really shouldn't ever be done in an
environment which has Active Directory.
Thanks!
Stephen
signature.asc
Description: Digital signature
done the scripts would be more complex and
challenging to use than any of the existing solutions.
I'd strongly suggest you consider one of the maintained backup solutions
that have already been written instead of inventing yet another one.
Thanks!
Stephen
signature.asc
Description: Digital signature
#x27;) order by title,dtype,source,used_for;
ERROR: syntax error in tsquery: " ma waterflux"
Remove either the "ma" or the "waterflux" and the query works.
What is causing the error?
(MA Waterflux is a product name.)
Cheers and thanks,
Stephen
--
Sent via pgsq
Greetings,
* mariusz (mar...@mtvk.pl) wrote:
> On Tue, 2017-07-04 at 17:55 -0400, Stephen Frost wrote:
> > > How is this done inside a shell script?
> >
> > Generally, it's not. I suppose it might be possible to use '\!' with
> > psql and then
Melvin,
* Melvin Davidson (melvin6...@gmail.com) wrote:
> On Wed, Jul 5, 2017 at 10:14 AM, Stephen Frost wrote:
> >Part of my concern is that such a script is unlikely to show any problems
> until it comes time to do a restore
> As previously stated, the script was used to set up
Greetings,
* Melvin Davidson (melvin6...@gmail.com) wrote:
> Stephen,
> >This script is a good example of why trying to take a PG backup using
> shell scripts isn't a good idea.
>
> Your criticism is noted, however, I have used it many times in the past
> with absolute
Greetings,
* Melvin Davidson (melvin6...@gmail.com) wrote:
> On Tue, Jul 4, 2017 at 5:55 PM, Stephen Frost wrote:
> > I'd recommend considering one of the existing PG backup tools which know
> > how to properly perform WAL archiving and tracking the start/stop points
> >
snapshot but that's not much
different from having to do WAL replay of the WAL generated during the
backup.
As for existing solutions, my preference/bias is for pgBackRest, but
there are other options out there which also work, such as barman.
Thanks!
Stephen
signature.asc
Description: Digital signature
about your use-case and see what we can do to make
RLS easier to use.
Thanks!
Stephen
signature.asc
Description: Digital signature
Greetings,
* Samuel Williams (space.ship.travel...@gmail.com) wrote:
> Thanks for all the suggestions Stephen.
>
> > That explain analyze shows a whole ton of heap fetches. When was the
> last time a VACUUM was run on this table, to build the visibility map?
> Without the vi
y when the results are
(relatively) slow moving.
> I'm open to any ideas or suggestions, ideally we can keep
> optimisations within the database, rather than adding a layer of
> caching on top.
If you need to go the materialized view route, I'd definitely recommend
doing that in the database rather than outside it.
Thanks!
Stephen
signature.asc
Description: Digital signature
ify alternative paths or external tools.
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.
Thanks!
Stephen
signature.asc
Description: Digital signature
welcome to ignore it.
Thanks!
Stephen
signature.asc
Description: Digital signature
nt you to user their
> car would do that for you.
There's a bit of a difference between buying a car and using a service
which is provided for free from a team of volunteers.
I agree that the "in an ideal world" wording is better. :)
Thanks!
Stephen
signature.asc
Description: Digital signature
o do something like that,
> the point is that I shouldn't have to.
I'm all for improving things and adding automation where it'll help, but
the infrastructure is basically run by volunteers. Making statements
like "I shouldn't have to" isn't the best approach to getting the
changes you'd like to see happen done.
Thanks!
Stephen
signature.asc
Description: Digital signature
o find a better approach to
testing your backup and recovery procedures as just checking checksums
doesn't tell you if there's been any database-level corruption, it'll
only identify filesystem-and-below corruption (well, and it might catch
some database-level bugs, but the coverage
ead, and it works
exactly as you describe above. As I mentioned, there are also tools for
performing incremental backups, which isn't quite the same as straight
WAL archiving.
Thanks!
Stephen
signature.asc
Description: Digital signature
hnology" that makes this
impossible or, really, even that difficult, it's more that there hasn't
been effort put into it simply because the file-level incremental
solution works quite well in most cases.
Thanks!
Stephen
signature.asc
Description: Digital signature
ot try to implement an incremental backup solution using
simple/naive tools like rsync with timestamp-based incrementals. It is
not safe.
Thanks!
Stephen
signature.asc
Description: Digital signature
ore the backup can be omitted from an incremental backup.
I strongly recommend you use one of the existing backup solutions for
PostgreSQL which know how to properly perform incremental backups. I
know at least pgBackrest and barman do, I'm not sure about others.
Thanks!
Stephen
signature.asc
Description: Digital signature
able to pull out data from it.
We are working to add S3 support to pgBackrest, but it's not there
today.
Thanks!
Stephen
signature.asc
Description: Digital signature
Greetings,
* Stephen Frost (sfr...@snowman.net) wrote:
> * Frank van Vugt (ftm.van.v...@foxi.nl) wrote:
> > Well, I didn't run into this issue with any of my db's that 'nicely' use
> > tables in various schema's, it was actually the one 'older'
nning time required when you have hundreds and
thousands of partitions, which is why I typically recommend against
using partitions-by-day unless you're only keeping a few months worth of
data.
Thanks!
Stephen
signature.asc
Description: Digital signature
ber of partitions, you have partitions with less than 20M rows
each and that's really small, month-based partitions with a BRIN would
probably work better). If you get to the point of having years worth of
daily partitions, you'd going to see increases in planning time.
Thanks!
Stephen
signature.asc
Description: Digital signature
unless you've only got a few days worth of data that make up those 800m
records.
Having hundreds of partitions leads to slow query planning time. There
is work happening to improve on this by having declarative partitions
instead of using CHECK constraints and the constrain exclusion
mechanism
ght it up, so maybe keeping one of those around
> isn't too bad an idea ;)
Yeah, I'll be including this in a regression test also, to make sure we
don't end up breaking this special case again in the future.
Thanks!
Stephen
signature.asc
Description: Digital signature
Greetings,
* Adrian Klaver (adrian.kla...@aklaver.com) wrote:
> On 02/13/2017 06:04 AM, Stephen Frost wrote:
> >* Adrian Klaver (adrian.kla...@aklaver.com) wrote:
> >>I am following this up to the point of not understanding what
> >>exactly changed between 9.5 and 9.6.
ystem that you're dumping the data from, and then set the ACLs to what
you want, they should be dumped out, even with a pg_dump -c. It's only
when you're using -c with the initdb-time public schema, and initdb-time
ACLs, that the issue arises.
Thanks!
Stephen
signature.asc
Description: Digital signature
e don't dump out any ACL
commands for the public schema. That ends up being incorrect in '-c'
mode because we drop the public schema in that mode and recreate it, in
which case we need to re-implement the ACLs which existed for the public
schema at initdb-time.
Thanks!
Steph
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost writes:
> > I'm not seeing a very simple answer for this, unfortunately.
>
> I'm inclined to argue that it was a mistake to include any non-pinned
> objects in pg_init_privs. The reason initdb leaves some o
the existing query) that is
just to get the info for the 'public' schema (and exclude the 'public'
schema from the first half of the query, of course).
Thanks for the report!
Stephen
signature.asc
Description: Digital signature
t
there is often a bit of a learning curve and when others point things
out that can sometimes be off-putting (not unlike our mailing lists..).
In any case, I'm happy to try and help out if people feel that there's
abusing of OPs or inappropriate behavior.
Thanks!
Stephen
signature.asc
Description: Digital signature
hey 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
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
* Thomas Kellerer (spam_ea...@gmx.net) wrote:
> Stephen Frost schrieb am 21.01.2017 um 22:37:
> >>So, there is no solution for my first question, we need if users enter the
> >>wrong password more than 5 times than their account gets locked and then
> >>only DBA will
rtificate-based auth or GSSAPI.
Thanks!
Stephen
signature.asc
Description: Digital signature
stgresql.org/docs/current/static/pgcrypto.html
Thanks!
Stephen
signature.asc
Description: Digital signature
7 feeding data
constantly to PG, do you really need a connection pooler for those?
Connection poolers make a lot of sense for environments where there's
lots of down-time on the connection, but the less down-time, the less
they make sense.
Thanks!
Stephen
signature.asc
Description: Digital signature
pure speculation, so
feel free to ignore me if I'm completely off-base here. :)
Thanks!
Stephen
signature.asc
Description: Digital signature
much of anything for connection-pooling scenarios.
I don't agree that this is unsolvable, but it would require things like
protocol-level changes which no one has had the gumption to work through
and propose.
In short, I agree with Guyren, there are features needed here that we
don't have and it would be a great deal better if we did.
Thanks!
Stephen
signature.asc
Description: Digital signature
can help is to
use a common user for 'read-only/public-access (or at least low-value)'
queries from the app, if there are such.
> Is this practical? Has anyone here done it? What might the caveats be?
Yes, yes, see above.
Thanks!
Stephen
signature.asc
Description: Digital signature
Daniel,
* Stephen Frost (sfr...@snowman.net) wrote:
> * Daniel Westermann (daniel.westerm...@dbi-services.com) wrote:
> > postgres@pgbox:/u01/app/postgres/local/dmk/ [PG961] pg_dumpall -V
> > pg_dumpall (PostgreSQL) 9.6.1
> > postgres@pgbox:/u01/app/postgres/local/dmk/ [PG9
.6/static/pageinspect.html
Thanks!
Stephen
signature.asc
Description: Digital signature
SQL database cluster into an SQL script file.
Hmm. Looks like an oversight, will see about fixing it.
Thanks!
Stephen
signature.asc
Description: Digital signature
On 23/12/16 16:20, Günce Kaya wrote:
Hi All,
I'm looking for an ER Data Modeller tool for postgresql. I use Navicat Premium
for postgresql and the tool has a modeller but I would like to display a
database modeller that belonging to a tables of an schema under a database.
If I use Navicat for m
uperuser to use the approach Joe
recommended.
Thanks!
Stephen
signature.asc
Description: Digital signature
ncluded in that set, of course, and depending on your requirments you
might wish to avoid updating them also.
Thanks!
Stephen
signature.asc
Description: Digital signature
ove it in the subsequent releases.
Thanks!
Stephen
signature.asc
Description: Digital signature
o I'd like to avoid useless complex indexes if possible.
A BRIN index should work pretty well in that scenario.
A btree index would most likely be better/faster for query time, but
more expensive to maintain.
Thanks!
Stephen
signature.asc
Description: Digital signature
On 08/10/16 17:16, Thomas Kellerer wrote:
Stephen Davies schrieb am 08.10.2016 um 02:57:
A follow-up question.
Once the bytea column is populated, how best to display the content in a
web page?
I have :
byte [] imgB;
ResultSet rs = st1.executeQuery("select pic from part where pno=&
On 07/10/16 19:24, Thomas Kellerer wrote:
Stephen Davies schrieb am 07.10.2016 um 10:46:
You can store the contents of a file in a bytea using plain JDBC no lo_import()
required
String sql = "insert into images (id, image_data) values (?,?)";
Connection con = ;
Fil
On 07/10/16 19:24, Thomas Kellerer wrote:
Stephen Davies schrieb am 07.10.2016 um 10:46:
You can store the contents of a file in a bytea using plain JDBC no lo_import()
required
String sql = "insert into images (id, image_data) values (?,?)";
Connection con = ;
Fil
On 07/10/16 18:48, Thomas Kellerer wrote:
Stephen Davies schrieb am 07.10.2016 um 09:12:
I am trying to use the import_bytea function described in various list posts
(PG version 9.3.14) in a jsp.
I get an error saying that only the super user can use server-side lo_import().
If I change the
.
Is there a better way to update a bytea column from an uploaded file (in this
case a small jpeg)?
Cheers and thanks,
Stephen
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
s, where the rows are
inserted in-order and never/very-rarely modified or deleted, this
approach would work very well.
Certainly, using this would be much cheaper than a seqscan/top-N sort,
for small values of 'N', relative to the number of rows in the table,
in those cases.
In ge
an to try and move an individual schema or
database.
Thanks!
Stephen
signature.asc
Description: Digital signature
On 29/09/16 05:47, jotpe wrote:
Does anybody know a Software for generating graphical entity relation models
from existing postgresql databases?
Best regards Johannes
I like SchemaSpy.
--
=
Stephen Davies Consulting
rivileges column for the public schema, which shows that
the 'postgres' role and the '' role (aka, 'public') have been granted
both USAGE and CREATE on that schema.
Thanks!
Stephen
signature.asc
Description: Digital signature
any effect.
Note that if you revoke all privielges from 'public' then only users who
have been explicitly granted access will be able to create or *use* any
objects in the public schema.
Generally, I revoke CREATE rights from the public schema, but leave
USAGE rights, as I then put truste
h if you have a lot of WAL then that can still take a bit of time
and disk space.
With sufficient interest and resources, we might be able to make it
happen, but I wouldn't expect it near-term. Until then, at least the
database-level option, as David mentioned, can be used.
Thanks!
Stephen
signature.asc
Description: Digital signature
t; is quite another.
It's not an insurmountable problem, though it's a bit painful. Still,
both the Debian-based and RedHat-based distributions demonstrate how it
can be done.
Thanks!
Stephen
signature.asc
Description: Digital signature
new enough about lower version to
> rejigger everything... just maybe it could do the reverse.
That might work if you opened the database in read-only mode, but not
once you start making changes.
Thanks!
Stephen
signature.asc
Description: Digital signature
Michael,
* Michael Paquier (michael.paqu...@gmail.com) wrote:
> On Tue, Jul 26, 2016 at 4:50 PM, Noah Misch wrote:
> > [Action required within 72 hours. This is a generic notification.]
> >
> > The above-described topic is currently a PostgreSQL 9.6 open item. Stephen,
&g
an get "out of memory".
> :(((
Do you have 100 CPUs on this system which apparently doesn't have 16G
of RAM available for PG to use?
If not, you should probably consider connection pooling to reduce the
number of PG sessions to something approaching the number of CPUs/cores
you have in the system.
Thanks!
Stephen
signature.asc
Description: Digital signature
our own
routines at times too. On the other hand, if there's a reason the glibc
folks don't want this, we should consider that..
Thanks!
Stephen
signature.asc
Description: Digital signature
biased towards and prefer pgBackRest, as I
helped start that project, but there are other tools, such as barman and
WAL-E, which would still be better than trying to implement everything
correctly on your own.
Thanks!
Stephen
signature.asc
Description: Digital signature
ew, it's the same as any other server
running Linux (I can SSH in, or tunnel my DB connection). To be honest
I'd rather have it this way than deal with the RDS interface.
Try to avoid those HIPAA compliance meetings though, they are terrible
and long.
-- Stephen
--
Sent via pgsql-ge
* Michael Paquier (michael.paqu...@gmail.com) wrote:
> On Mon, Jun 6, 2016 at 9:51 PM, Stephen Frost wrote:
> > * Vik Fearing (v...@2ndquadrant.fr) wrote:
> >> On 03/06/16 04:32, Michael Paquier wrote:
> >> > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar
>
Jean-Marc,
* Jean-Marc Lessard (jean-marc.less...@ultra-ft.com) wrote:
> Stephen Frost [sfr...@snowman.net] wrote:
> > The database owner operating system user has to be trusted, along with any
> > superusers in the database, but if you assume those, then having PG manage
&g
* Vik Fearing (v...@2ndquadrant.fr) wrote:
> On 03/06/16 04:32, Michael Paquier wrote:
> > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar
> > wrote:
> >> On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost wrote:
> >>> Given the usefulness of this specific query and
o have access
to the postgres unix user account on the system, they shouldn't give you
a PG superuser account.
Thanks!
Stephen
signature.asc
Description: Digital signature
an be updated via ALTER SYSTEM
through psql also.
Issuing a 'pg_ctl restart' via COPY PROGRAM isn't a good idea and may
not work, though I suppose you could try if you really wish to.
Thanks!
Stephen
signature.asc
Description: Digital signature
David,
* David G. Johnston (david.g.johns...@gmail.com) wrote:
> On Thu, Jun 2, 2016 at 4:29 PM, Stephen Frost wrote:
>
> > * Sameer Kumar (sameer.ku...@ashnik.com) wrote:
> > > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov,
> > > wrote:
> > > > Can I li
y either.
Something along the lines of 'pg_xlog_file_list()', perhaps. There is a
check in check_postgres.pl which could take advantage of this also.
Should be a very straight-forward function to write, perhaps good as a
starter project for someone.
Thanks!
Stephen
signature.asc
Description: Digital signature
1 - 100 of 652 matches
Mail list logo
