Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

[Shulgin, Oleksandr]

On Apr 22, 2016 19:46, "Melvin Davidson"  wrote:
>
>
> First, tahnk you for your feedback Alex.
>
> "IMO, every time it was conclusively demonstrated that when you consider
dump/restore semantics, this feature can have exactly zero value if
implemented *inside pg_catalog*.  And it would have to be a pretty invasive
change (it's not enough to just add the attribute, you also need to touch
probably a dozen of places where it will be populated or read), so without
any positive effect it results in negative effect overall."

I find yor lack of proper email quoting skills disturbing...

> Actually, there is no harm in this. If a database is dumped and restored
to a new database, then there is no need to reset the value of relcreate,
as it is a copy of the original db.

Yes, but that means that the timestamps must be part of the dump file,
which means in turn they can be altered before the restore or--if
implemented as separate commands like ALTER TABLE ... SET TIMESTAMP--at any
point in time, so there is little to zero value in having the timestamps in
the first place. This was already discussed in earlier threads.

> If restoring to the same database. then, by definition, it is a data only
restore, as objects are already in existence.

OK

> There is no need to touch anyplace other than pg_class to capture when an
object is created, and
> leaving relcreated NULL for existing objects has no negative effect.

That is one way to think about it, I do not necessarily agree with it.

> "don't buy the example of "DELETE/DROP TABLE" based on relcreated field.
Do you, by chance, have any other use case?"
>
> Yes, it would greatly assist DBA's in tracking down objects created
outside normal hours/days.
> eg: SELECT * FROM pg_class WHERE EXTRACT(DOW FROM relcreated) IN (0,6);

I hope you do not intend to drop the objects found in this way without a
review? What if such an object was created by a user which is in a
geographically distant location compared to the server and it was still
Friday there, but it was already Saturday on the server's clock? You likely
need to know the user name in addition to the timestamp, so you can verify
the user's reasons.

> I also mentioned that this is already in the catalogs of Oracle and SQL
Server.

This sort of argument doesn't help the discussion: there should be a good
reason to add the feature and merely pointing out that others already doing
that is not a good reason, IMO.

> "Apart from created timestamp would you not like to also know the
user/role who has created it?  What about updates (using ALTER
TABLE)--would you want to know when that *last* happened and who did that?
Would you want to know what exactly was altered?  Would you want to know
the history *before* the last update?  Finally, if someone drops the table,
you can say good bye to its pg_catalog records and there's no hope to know
who did that and when (or if that table has even existed to start with)."
>
> At this point, I am only interested in capturing the creation of
unauthorized objects by rogue users.

If rogue users can create objects in your schema you have bigger problems
than tables created outside of working hours (and frankly, I don't see any
harm in that). Can your rogue users also drop tables?

Your better bet is to keep rogue users out: using GRANTs and pg_hba.conf.

> If the query shows objects created at suspicious times. A further review
of the logs would reveal necessary
> additional info. Yes, it would be good to capture ALTER's also, but that
complicates things, so I am only
> looking for a simple, safe change.

If you are going to review the logs anyway, why not just set
log_statements=ddl and use logs as the source of timestamp data (together
with user, connection details, etc.)?

> I really wish people would stop focusing on when features will not work
and consider more of the benefit they will gain from the situations where
they do work.
> I also cannot understand why people are paranoid about adding a simple
nullable timestamp column to a system catalog,

There yet to be found a good reason to do that. No one is going to make
even a "simple change" just for you to try and see the idea fails in
practice due to all the other things you didn't think about. But you can
make such a change for yourself and run a patched version for a while if
you want--no one can stop you here.

> especially since no one gave any
> thought to the adverse effect caused by renaming a column ( procpid to
pid) in pg_stat_activity when going from 9.1 to 9.2. I bet more than a few
DBA's had to
> change their scripts that monitored activity.

Well, I didn't participate in that change discussion and I agree it is a
bit annoying then these view change the column names.

--
Alex

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

[Shulgin, Oleksandr]

On Thu, Apr 21, 2016 at 6:55 PM, Melvin Davidson 
wrote:

> And so far, NO ONE has shown any proof that this enhancement could
> possibly cause ANY negative result.
>

Searching through the list archives[1] I can see that you've asked this
question a number of times already.  And I'm pretty sure it was asked quite
a number of times by the others.

IMO, every time it was conclusively demonstrated that when you consider
dump/restore semantics, this feature can have exactly zero value if
implemented *inside pg_catalog*.  And it would have to be a pretty invasive
change (it's not enough to just add the attribute, you also need to touch
probably a dozen of places where it will be populated or read), so without
any positive effect it results in negative effect overall.


> All that has been presented so far are corner cases where this "might" not
> be useful.
> If the PostgreSQL developers are really worried about unexpected
> drawbacks, then, based on that,  ALL future development should stop
> immediately.
> This is total insanity! I am asking for a simple, safe enhancement that
> would add what compatibility with what is already in other databases, yet
> everyone seems to be terrified about it.
> We have already modified system catalogs previously with no ill effect.
>

I believe system catalogs are modified on a regular basis with every major
release.  But in every instance there has to be a good reason for a change.

So please, someone present a logical explanation of why this should not be
> done, or how it will negatively impact the PostgreSQL project.
> If you cannot do so, then start thinking positively.
>

As said before a number of times: what you propose looks easy, but it's
just the tip of an iceberg.  Even if the community comes to an agreement
what dump/restore semantics should be and it is implemented, the feature is
still not *that* useful on its own to justify its existence (no, I don't
buy the example of "DELETE/DROP TABLE" based on relcreated field. Do you,
by chance, have any other use case?)

Apart from created timestamp would you not like to also know the user/role
who has created it?  What about updates (using ALTER TABLE)--would you want
to know when that *last* happened and who did that?  Would you want to know
what exactly was altered?  Would you want to know the history *before* the
last update?  Finally, if someone drops the table, you can say good bye to
its pg_catalog records and there's no hope to know who did that and when
(or if that table has even existed to start with).

When you just start thinking in this direction, it becomes apparent that a
proper audit solution is a much better fit to tackle these problems.  There
are features continuously added in the recent releases that will facilitate
building such solutions in form of extensions: DDL event triggers and
Logical decoding, to name a few.

Previous to yesterday, nowhere on the PostgreSQL site was it stated WHERE
> to present enhancement requests.
>

There is plenty of information on PostgreSQL sites about this[2,3,4].  Are
you suggesting something was add yesterday on top of that?

Now that it has been verified this is the correct list,
>

Probably it is the most appropriate one, unless you have the patch ready
(then it would be for -hackers).  I'm still puzzled as to how have you
found that completely unrelated feature request voting site given the
abundance of information on the official sites and lack of links to that
site from there.

It is true that some visibility of what majority of users consider to be
the most useful enhancement could benefit the project, but it has to be
maintained by the community in order to provide some value.  Otherwise it
is going to have only the negative impact: an impression that PostgreSQL
developers doesn't listen to the users.

There still exists no formal requirements for presenting an enhancement
> request.
>

Just follow the requirements for a good problem report, especially[5].
After all you have a problem of a missing feature, right?


> WHY am I being vilified for making a simple request? How is it that
> developers proceed with other enhancements, yet so much negative attention
> is being given to my request because of unjustified fear that something
> bad will happen?
>

Less colorful^W^W plain text mails without top-posting might help here.
Seriously, not everyone has the time to present the same arguments over and
over again: searching the archives should have given you some perspective
on the destiny of this feature request.

Should we really put this on Todo with a mark that we actually don't want
it?

Regards,
--
Alex

[1] http://www.postgresql.org/search/?m=1=relcreated
[2] http://www.postgresql.org/support/
[3] https://wiki.postgresql.org/wiki/FAQ#Where_can_I_get_support.3F
[4] https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
[5]
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems#Things_Not_To_Do

Re: [GENERAL] Enhancement Request

[Shulgin, Oleksandr]

On Wed, Apr 20, 2016 at 3:47 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, Apr 19, 2016 at 5:59 PM, Melvin Davidson 
> wrote:
>
>>
>> Enhancement or feature requests should probably go to  *Custormer
>> Feedback *
>> https://postgresql.uservoice.com/forums/21853-general
>>
>
> ​I'm on the PostgreSQL home page: what series of links would lead to "
> uservoice.com" so that I can submit my feature request?
>
> If that site is going to be recommended over posting to pgsql-general it
> should feature prominently on the main website.
>
> ​I've seen or heard nothing that suggests anywhere except pgsql-general,
> this list, is the correct place to submit such requests.  I do think we are
> lacking sufficient communication in this area but would rather see that
> corrected and formalized before we go pointing people to third-party sites.
>

It is interesting that this channel was opened at least in the middle 2009
or before (judging by the feature/comments timestamps).  It would be nice
if uservoice provided any hint or contact for the owner of the channel,
because right now it appears to be a total mystery.

I'm not sure if the owner is subscribed to -general, I wouldn't be
surprised if not.  Otherwise, please speak up. :-)

I think that such user communication channel can be useful if maintained
properly, and if the rest of the community would be aware of its
existence.  What I dislike right now, is that it pretends to be official
channel without any disclaimer or references to the actual official
communication channels (which are the mailing lists and IRC, AFAIK).

Regards,
--
Alex

Re: [GENERAL] how to switch old replication Master to new Standby after promoting old Standby

[Shulgin, Oleksandr]

On Mon, Mar 14, 2016 at 6:28 PM, John Lumby  wrote:

>
> From: johnlu...@hotmail.com
> To: pgsql-general-ow...@postgresql.org
> Subject: how to switch old replication Master to new Standby after
> promoting old Standby
> Date: Mon, 14 Mar 2016 13:23:29 -0400
>
> In a scenario involving replication where no failure occurs but I want to
> interchange Master <->Standby,
> *and* want to avoid making another full base backup and rsyncing it across,
> I have found it easy to promote old Standby to new Master with pg_ctl
> promote,
> but very difficult to restart the old Primary as a new Standby *without*
> performing new base backup.
>
> Assume current 9.5.1 and using streaming replication with a named
> replication slot if relevant
>
> Second assumption - I am able to temporarily prevent any relational
> updates to the database before I start the switchover
> e.g. the
> default_transaction_read_only = on
> setting together with being able to control what transactions do if
> anything
>
> So I reach a point where both systems have postgresql running without any
> replication,
> both have identical content in all databases (that I can control),
> and I am willing to tolerate short restarts if need be and also to
> scp/rsync the contents of pg_xlog
> and other small files but *not* the entire cluster directory or any
> database base directories.
>
> What do I do next?
>
> Here is what I have found seems to work but I am not sure it is robust:
>
> 1. shut down both new Master and intended-to-be-new-Standby
> 2. on intended-to-be-new-Standby, remove the entire content of pg_xlog and
> the global/pg_control
> 3. from new Master , tar + scp the entire content of pg_xlog and the
> global/pg_control to intended-to-be-new-Standby
>

That does seem like a very risky strategy to me.  Have you taken a look at
pg_rewind (which is now part of the distribution)?

--
Alex

Re: [GENERAL] pg_restore real file size

[Shulgin, Oleksandr]

On Fri, Feb 26, 2016 at 5:30 AM, John R Pierce  wrote:

> On 2/25/2016 8:26 PM, drum.lu...@gmail.com wrote:
>
>
> I'm doing the pg_restore now in a 1.5TB file:
>
> *# ls -la*
>
> postgres postgres 1575324616939 Feb 20 13:55 devdb_0.sql
>
> But, the restore has gone over 1.6 TB
>
>
> the dump file does not contain the indexes, just CREATE INDEX statements
>

Not to mention that on-disk format is quite different from the SQL dump.
Due to row and page headers the on disk format could occupy more space, on
the other hand if you have a lot of numeric data which can be represented
compactly in the binary format, the restored database might occupy less
space in the end.

--
Alex

Re: [GENERAL] sslcompression / PGSSLCOMPRESSION not behaving as documented?

[Shulgin, Oleksandr]

On Thu, Feb 18, 2016 at 11:37 PM, Jeff Janes  wrote:

> On Fri, Jan 16, 2015 at 10:34 AM, Maciek Sakrejda 
> wrote:
> > Thanks, everyone. That seems to be it. I still haven't gotten it to work
> (I
> > tried setting OPENSSL_DEFAULT_ZLIB=true in
> > /etc/postgresql/9.2/main/environment and restarting the server, then
> adding
> > the same env var when connecting with the client), but now that I know
> where
> > the problem is, I think I can work through it.
>
> Did you ever get it to work on Ubuntu?  If so, what did you have to do?
>
> OPENSSL_DEFAULT_ZLIB doesn't seem to do anything on Ubuntu 14.04.  It
> is suggested it should work on earlier versions
> (http://www.ubuntu.com/usn/USN-1898-1/) but there is no mention of it
> on newer versions.
>

I can confirm that this env var has the expected effect on Ubuntu 12.04,
but newer versions such 14.04 come with OpenSSL complied without zlib
altogether, so there is no way to enable this short of recompiling the
openssl lib, unfortunately.

--
Alex

Re: [GENERAL] Tutorial on How to Compile PostgreSQL 9.5 for Windows 64bit

[Shulgin, Oleksandr]

On Tue, Jan 26, 2016 at 4:21 PM, Edson F. Lidorio 
wrote:

>
>
> On 25-01-2016 16:46, Igal @ Lucee.org wrote:
>
> Hi Everybody,
>
> I have posted a video tutorial on How to Compile PostgreSQL 9.5 for
> Windows 64bit
> https://www.youtube.com/watch?v=-BJmuZT5IPE
>
> It was quite difficult for me to figure it out, so hopefully it will make
> life easier for
> the next guy (or gal).
>
> --
>
> Igal Sapir
> Lucee Core Developer
> Lucee.org 
>
> Hello,
> Excellent Tutorial,
> How to compile the master version 9.6 Dev, for Linux?
>

Basically, as outlined on this page:
https://wiki.postgresql.org/wiki/Compile_and_Install_from_source_code

I also find "apt-get build-dep postgresql-common" command particularly
useful.

-- 
Alex

Re: [GENERAL] help:error while running postgres after installing postgresql server from source code

[Shulgin, Oleksandr]

On Sat, Jan 23, 2016 at 6:42 PM, Shubham Barai 
wrote:
>
> I was trying to install postgresql from source code on ubuntu.I followed
all the instructions and installed all the packages.I was also able to
create database cluster but when I executed this command 'postgres -D
/usr/local/pgsql/data' I got this
>
>  $ postgres -D /usr/local/pgsql/data
> > The program 'postgres' is currently not installed. You can install it by
> > typing:
> > sudo apt-get install postgres-xc

That really depends on where you did install the binary files.  Did you
specify a --prefix option when running configure script?  If so, you might
need a command like the following:

$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data

Actually, assuming you've created the database by using the newly installed
initdb command, you just need to replace "initdb" with "postgres" in that
command.

--
Alex

Re: [GENERAL] Moving a large DB (> 500GB) to another DB with different locale

[Shulgin, Oleksandr]

On Fri, Jan 15, 2016 at 1:02 PM, Andreas Joseph Krogh 
wrote:

> På torsdag 14. januar 2016 kl. 00:34:51, skrev Jim Nasby <
> jim.na...@bluetreble.com>:
>
> On 1/13/16 2:39 PM, Andreas Joseph Krogh wrote:
> > Where can I find more info about how to use and configure pg_logical to
> > replicate a 9.4 DB to 9.5?
>
> http://2ndquadrant.com/en/resources/pglogical/
>
>
> Thanks, I found detailed instructions in
> /usr/share/doc/postgresql-9.5-pglogical/README.md.gz
> Any chance of putting in online?
>
> I see that wal_level = 'logical', and that is a problem for us as we
> already use wal_level = 'hot_standby' on this installation as it replicates
> to another server.
>
> Is it possible to use pglogical together with hot_standby
> streaming-replication?
>

Well, the wal_level change is just a matter of database restart: you got to
do that once in a while anyway, e.g. for minor version updates.  I would
expect you only need this wal_level on the walsender side, thus for
pglogical_output, the logical decoding plugin.

--
Alex

Re: [GENERAL] Moving a large DB (> 500GB) to another DB with different locale

[Shulgin, Oleksandr]

On Fri, Jan 15, 2016 at 3:41 PM, Andreas Joseph Krogh <andr...@visena.com>
wrote:

> På fredag 15. januar 2016 kl. 14:33:24, skrev Shulgin, Oleksandr <
> oleksandr.shul...@zalando.de>:
>
> On Fri, Jan 15, 2016 at 1:02 PM, Andreas Joseph Krogh <andr...@visena.com>
> wrote:
>>
>>
>> I see that wal_level = 'logical', and that is a problem for us as we
>> already use wal_level = 'hot_standby' on this installation as it replicates
>> to another server.
>>
>> Is it possible to use pglogical together with hot_standby
>> streaming-replication?
>>
>
> Well, the wal_level change is just a matter of database restart: you got
> to do that once in a while anyway, e.g. for minor version updates.  I would
> expect you only need this wal_level on the walsender side, thus for
> pglogical_output, the logical decoding plugin.
>
>
> My point is that we cannot not have streaming-replication, so we need to
> keep wal_level = 'hot_standby' AFAIU. Is there a way to do both
> streaming-replication *and* pglogical for just replicating one of may
> databases in the same cluster?
>

But logical is "greater than" hot_standby, so you can still have streaming
replication with wal_level = logical.

--
Alex

Re: [GENERAL] [BUGS] permissions.

[Shulgin, Oleksandr]

[not a bug, moving to -general]

On Tue, Jan 12, 2016 at 12:46 PM, Frik Brits  wrote:

> Hello,
> What is to be done here, below between the lines is an excerpt from my
> bash console if I want to start PostgreSQL.
>
>
> -
> frik@linux-cj2v:/etc/alternatives> postgres -D /bigdisk/data/postgres &
> [1] 10438
> frik@linux-cj2v:/etc/alternatives> 2016-01-12 13:09:29 SAST   FATAL:
> could
> not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": Permission
> denied
>
> --
>
> mySQL has the same problems. Perhaps worse.I know u do not do mySQL stuff,
> but
> still.
>
> I can go fix this by changing the permissions of /var/run and
> /var/run/postgresql/ direc tories but then one will have to change it every
> single time I want to start the databse. O by the way starting it as a
> service
> is even worse because the then it wants to work in it own data directory.
>
> Can this not be fixed, I mean linux is linux whichever flavour one my want
> to
> use?.
>
> Fortunately not all linux programs have these strange permissions hickups.
>

Typically a postgres installation configured in such a way expects that the
master process is started by the user named postgres.  You can achieve that
by using sudo(1) command, e.g: sudo -u postgres COMMAND

In your example:

$ sudo -u postgres postgres -D /bigdisk/data/postgres

Not sure why are you trying to start it in foreground.  Please refer to
documentation for pg_ctl(1) and your distribution init system.  Something
like the following should have equivalent effect (that is, starting
postgres):

$ sudo pg_ctl -D /bigdisk/data/postgres start

OR

$ sudo service postgresql start

Debian and derivative systems such as Ubuntu provide pg_ctlcluster(1)
command to manage multiple clusters and PostgreSQL versions.

Can you elaborate on the problems you encounter when PostgreSQL is started
as a service?

--
Alex

Re: [GENERAL] PostgreSQL upgrade 9.3.4 -> 9.3.10

[Shulgin, Oleksandr]

On Tue, Jan 12, 2016 at 10:28 AM, Dev Kumkar 
wrote:
>
> Hello,
>
>
> I want to upgrade my database from version 9.3.4 to 9.3.10.
>
> For this task, do I need to upgrade database using pg_upgrade utility?
> http://www.postgresql.org/docs/9.3/static/pgupgrade.html
>
>
> From the details it looks like for minor version upgrade pg_upgrade
utility is not required.
> "pg_upgrade (formerly called pg_migrator) allows data stored in
PostgreSQL data files to be upgraded to a later PostgreSQL major version
without the data dump/reload typically required for major version upgrades,
e.g. from 8.4.7 to the current major release of PostgreSQL. It is not
required for minor version upgrades, e.g. from 9.0.1 to 9.0.4."
>
> Can someone please provide more details here and also what steps needs to
be done to upgrade to 9.3.10 level?

Hello,

You don't need pg_upgrade for minor version upgrades.  Please refer to the
documentation: http://www.postgresql.org/docs/9.3/static/upgrading.html

"Minor releases never change the internal storage format and are always
compatible with earlier and later minor releases of the same major version
number, e.g., 8.4.2 is compatible with 8.4, 8.4.1 and 8.4.6. To update
between compatible versions, you simply replace the executables while the
server is down and restart the server. The data directory remains unchanged
— minor upgrades are that simple."

--
Alex

Re: [GENERAL] [BUGS] postgresql table data control

[Shulgin, Oleksandr]

On Sat, Oct 17, 2015 at 1:26 AM, 許耀彰  wrote:

> Dear Support Team,
> How can we know each table data increase day by day? It mean how do we get
> how many data produce today,included which data? Thank you.
>

[moving from bugs@]

Please refer to these SQL-level functions:

pg_relation_size()
pg_total_relation_size()
pg_size_pretty()

http://www.postgresql.org/docs/current/static/functions-admin.html