Re: [GENERAL] Backward compatibility

[Igor Korot]

Hi, guys,
Below query does not even run:

SELECT version(), substring( version() from position( '\s' in version() ) );

Could you spot the error?

On Fri, Jul 21, 2017 at 12:11 PM, Igor Korot  wrote:
> David et al,
>
> On Fri, Jul 21, 2017 at 12:00 PM, David G. Johnston
>  wrote:
>> On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot  wrote:
>>>
>>> MySQL uses this:
>>> https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html.
>>> Is it safe to assume that PostgreSQL calculates the version the same way?
>>
>>
>> Yes and no.  Things are changing with this next release.  The next two major
>> releases will be:
>>
>> 10.x  (or 10.0.x using historical nomenclature - 1000xx)
>> 11.x (or 11.0.x using historical nomenclature - 1100xx)
>>
>> For prior releases the major versions are:
>>
>> 9.2.x
>> 9.3.x
>> 9.4.x
>> 9.5.x
>> 9.6.x
>>
>> If you want to consider the 9 to be "major" and the .[2-6] to be minor for
>> mechanical purposes that's fine but the change from 9.5 to 9.6 is a major
>> change with backward incompatibilities - which a minor change doesn't allow.
>> In the new setup the thing you call "minor" will always remain at zero in
>> order to eventually mitigate the need to have this kind of discussion. Since
>> it is always going to be "0" we simply omit printing it.
>
> I just need to split the version by ".".
>
> But if the next releases will not increment second value and will
> number the releases
> as 10.0.0, 10.0.1, 10.0.2, then this schema won't work.
>
> Thank you.
>
>>
>> David J.


-- 
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 csv logging

[David G. Johnston]

It is customary to indicate when you've posted the same question to other
forums - in this case SO.

https://dba.stackexchange.com/questions/180263/postgres-log-query-and-command-tag-to-csv

As the comment there says your config and your output seem at odds.  Though
I think your confusion is still adequately represented.

On Wed, Jul 19, 2017 at 4:41 PM, Alessandro_feliz <
alessandro_fe...@hotmail.com> wrote:

> Why is the first line, that has the query executed, saying that the command
> tag is "idle", shouldn't it say "SELECT"?


"Command tag: type of session's current command" - given your example I
suppose this means "initial state of the session" as opposed to being a
category tag of what the current line is doing.  What you are seeing is
"start at idle and parse" the incoming statement text.  That is one action
- parse.  The subsequent action is "execute" the contents of the SELECT
that is now in the active state of the session.

If the above is correct the docs could be more clear since I'd agree that
your interpretation of the field seems more logical - though knowing
initial state is quite useful in its own right.

David J.

Re: [GENERAL] Support for \u0000?

[Matthew Byrne]

I see.  Thanks for the quick responses!

On Wed, Jul 19, 2017 at 11:32 PM, Tom Lane  wrote:

> Matthew Byrne  writes:
> > Would a more feasible approach be to introduce new types (say, TEXT2 and
> > JSONB2 - or something better-sounding) which are the same as the old ones
> > but add for support \u and UTF 0?  This would isolate nul-containing
> > byte arrays to the implementations of those types and keep backward
> > compatibility by leaving TEXT and JSONB alone.
>
> The problem is not inside those datatypes; either text or jsonb could
> trivially store \0 bytes.  The problem is passing such values through
> APIs that don't support it.  Changing those APIs would affect *all*
> datatypes.
>
> regards, tom lane
>

Re: [GENERAL] UPDATE column without FK fires other FK triggers constraint check

[Luca Looz]

Thanks for the explanation!
Can these checks be implemented or the data needed is not there and adding
it will only add an overhead for the majority of use cases?

2017-07-19 20:42 GMT+02:00 Tom Lane :

> Luca Looz  writes:
> > After some tests it seems that this happens when the same row is covered
> by
> > more than 1 update in the same transaction even without any change.
> > Is this an expected behavior? Why it happens?
>
> Yes, see comment in RI_FKey_fk_upd_check_required:
>
>  * If the original row was inserted by our own transaction, we
>  * must fire the trigger whether or not the keys are equal.
> This
>  * is because our UPDATE will invalidate the INSERT so that the
>  * INSERT RI trigger will not do anything; so we had better do
> the
>  * UPDATE check.  (We could skip this if we knew the INSERT
>  * trigger already fired, but there is no easy way to know
> that.)
>
> Although this is talking about the BEGIN; INSERT; UPDATE; COMMIT case,
> the code has no way to tell that apart from BEGIN; UPDATE; UPDATE; COMMIT.
>
> regards, tom lane
>

Re: [GENERAL] Support for \u0000?

[Matthew Byrne]

Thanks for the response Tom.  I understand this would be a mammoth task.

Would a more feasible approach be to introduce new types (say, TEXT2 and
JSONB2 - or something better-sounding) which are the same as the old ones
but add for support \u and UTF 0?  This would isolate nul-containing
byte arrays to the implementations of those types and keep backward
compatibility by leaving TEXT and JSONB alone.

Matt

On Wed, Jul 19, 2017 at 7:30 PM, Tom Lane  wrote:

> Matthew Byrne  writes:
> > Are there any plans to support \u in JSONB and, relatedly, UTF code
> > point 0 in TEXT?
>
> No.  It's basically never going to happen because of the widespread use
> of C strings (nul-terminated strings) inside the backend.  Making \0 a
> legal member of strings would break all those internal APIs, requiring
> touching far more code than anyone would want to do.  It'd likely break
> a great deal of client-side code as well.
>
> regards, tom lane
>

[GENERAL] Postgres csv logging

[Alessandro_feliz]

I am trying to log executed queries into a csv file.

My main objective is to log the command tag and query. For that I enabled
logging in the postgresql.conf, my configs are the following:

#--
# ERROR REPORTING AND LOGGING
#--

# - Where to Log -

log_destination = 'stderr,csvlog'   # Valid values are
combinations of
# stderr, csvlog, syslog, and
eventlog,
# depending on platform.  csvlog
# requires logging_collector to be
on.

# This is used when logging to stderr:
logging_collector = on  # Enable capturing of stderr and
csvlog
# into log files. Required to be on
for
# csvlogs.
# (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log'# directory where log files are
written,
# can be absolute or relative to
PGDATA
log_filename = 'postgresql-%a-%H-%M.log'# log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600   # creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = on   # If on, an existing log file with
the
# same name as the new log file will
be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on
restarts
# or size-driven rotation.  Default
is
# off, meaning append to existing
files
# in all cases.
log_rotation_age = 10   # Automatic rotation of logfiles
will
# happen after that time.  0
disables.
log_rotation_size = 10240   # Automatic rotation of
logfiles will
# happen after that much log output.
# 0 disables.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# This is only relevant when logging to eventlog (win32):
#event_source = 'PostgreSQL'

# - When to Log -

#client_min_messages = log  # values in order of decreasing
detail:
#   debug5
#   debug4
#   debug3
#   debug2
#   debug1
#   log
#   notice
#   warning
#   error

#log_min_messages = info# values in order of decreasing
detail:
#   debug5
#   debug4
#   debug3
#   debug2
#   debug1
#   info
#   notice
#   warning
#   error
#   log
#   fatal
#   panic

#log_min_error_statement = info # values in order of decreasing
detail:
#   debug5
#   debug4
#   debug3
#   debug2
#   debug1
#   info
#   notice
#   warning
#   error
#   log
#   fatal
#   panic (effectively off)

log_min_duration_statement = 0  # -1 is disabled, 0 logs all
statements
# and their durations, > 0 logs only
# statements running at least this
number
# of milliseconds


# - What to Log -

#debug_print_parse = 

Re: [GENERAL] Bug in postgres 9.6.2?

[Tom Lane]

greigwise  writes:
> If I can provide a pg_dump backup with a db where I can reproduce the error
> and then also my postgresql.conf along with the query, would that be what
> you need for a test case?

Sounds like enough.

regards, tom lane


-- 
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] Bug in postgres 9.6.2?

[greigwise]

If I can provide a pg_dump backup with a db where I can reproduce the error
and then also my postgresql.conf along with the query, would that be what
you need for a test case?

Thanks,
Greig



--
View this message in context: 
http://www.postgresql-archive.org/Bug-in-postgres-9-6-2-tp5972185p5972450.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Backward compatibility

[Igor Korot]

David et al,

On Fri, Jul 21, 2017 at 12:00 PM, David G. Johnston
 wrote:
> On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot  wrote:
>>
>> MySQL uses this:
>> https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html.
>> Is it safe to assume that PostgreSQL calculates the version the same way?
>
>
> Yes and no.  Things are changing with this next release.  The next two major
> releases will be:
>
> 10.x  (or 10.0.x using historical nomenclature - 1000xx)
> 11.x (or 11.0.x using historical nomenclature - 1100xx)
>
> For prior releases the major versions are:
>
> 9.2.x
> 9.3.x
> 9.4.x
> 9.5.x
> 9.6.x
>
> If you want to consider the 9 to be "major" and the .[2-6] to be minor for
> mechanical purposes that's fine but the change from 9.5 to 9.6 is a major
> change with backward incompatibilities - which a minor change doesn't allow.
> In the new setup the thing you call "minor" will always remain at zero in
> order to eventually mitigate the need to have this kind of discussion. Since
> it is always going to be "0" we simply omit printing it.

I just need to split the version by ".".

But if the next releases will not increment second value and will
number the releases
as 10.0.0, 10.0.1, 10.0.2, then this schema won't work.

Thank you.

>
> David J.


-- 
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] Backward compatibility

[David G. Johnston]

On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot  wrote:

> MySQL uses this:
> https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html.
> Is it safe to assume that PostgreSQL calculates the version the same way?
>
​
Yes and no.  Things are changing with this next release.  The next two
major releases will be:

10.x  (or 10.0.x using historical nomenclature - 1000xx)
11.x (or 11.0.x using historical nomenclature - 1100xx)

For prior releases the major versions are:

9.2.x
9.3.x
9.4.x
9.5.x
9.6.x

If you want to consider the 9 to be "major" and the .[2-6] to be minor for
mechanical purposes that's fine but the change from 9.5 to 9.6 is a major
change with backward incompatibilities - which a minor change doesn't
allow.  In the new setup the thing you call "minor" will always remain at
zero in order to eventually mitigate the need to have this kind of
discussion. Since it is always going to be "0" we simply omit printing it.

David J.

Re: [GENERAL] Backward compatibility

[Igor Korot]

Hi, guys,

On Thu, Jul 20, 2017 at 11:58 PM, Tom Lane  wrote:
> John R Pierce  writes:
>> On 7/20/2017 8:40 PM, Tom Lane wrote:
>>> Hm, we need to update that text for the new 2-part version numbering
>>> scheme, don't we?
>
>> will 10 return like 100100 if its 10.1, or 11 ?
>
> The latter.  The two middle digits will be zeroes henceforth, unless
> we somehow get into a situation where the minor version needs to
> exceed 99.

MySQL uses this:
https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html.
Is it safe to assume that PostgreSQL calculates the version the same way?

Thank you.

>
> regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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_restore misuse or bug?

[Jordan Gigov]

This is on version 9.5, 9.6 and 10beta2. I could probably make a
test-case over the weekend if I'm at home.

On 21 July 2017 at 17:03, Tom Lane  wrote:
> Jordan Gigov  writes:
>> When running pg_restore as the superuser it gives the following error
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 3253; 0 320628
>> MATERIALIZED VIEW DATA combined_query_data web_user
>> pg_restore: [archiver (db)] could not execute query: ERROR:
>> permission denied for relation first_table_in_from_list
>> Command was: REFRESH MATERIALIZED VIEW combined_query_data;
>
> What PG version is this?  Can you provide a self-contained test case?
>
>> I see no reason why the superuser would get a "permission denied"
>> error.
>
> Matview queries are run as the owner of the matview, so this isn't
> as surprising as all that.  But if the matview works in your normal
> usage, then pg_dump must be doing something wrong, perhaps emitting
> grants in the wrong order.
>
> regards, tom lane


-- 
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] Schemas and foreign keys

[marcelo]

Thank you, Andreas.
Your answer closes this thread.

On 21/07/17 11:07, Andreas Kretschmer wrote:



Am 21.07.2017 um 14:58 schrieb marcelo:
Recently I asked regarding schemas, and received very useful answers. 
I conclude that I can put some tables in one schema and left others 
in the public one.
If my app selects some schema, the backend will found automatically 
the absent tables in "public".

So far, so good.
But what about foreign keys? At least, I will have foreign keys from 
the tables in the specified schema to the tables in "public", because 
I'm thinking that the tables in "public" would be references, while 
the tables residing in the specified schema will be the transactional 
ones.

TIA
Marcelo




that's no problem:

test=# create schema demo1;
CREATE SCHEMA
test=*# create schema demo2;
CREATE SCHEMA
test=*# create table master_table(id int primary key);
CREATE TABLE
test=*# create table demo1.demo_table(id int primary key, master_id 
int references public.master_table);

CREATE TABLE
test=*# create table demo2.demo_table(id int primary key, master_id 
int references public.master_table);

CREATE TABLE


Regards, Andreas





--
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] Schemas and foreign keys

[Albe Laurenz]

marcelo wrote:
> Recently I asked regarding schemas, and received very useful answers. I
> conclude that I can put some tables in one schema and left others in the
> public one.
> If my app selects some schema, the backend will found automatically the
> absent tables in "public".
> So far, so good.
> But what about foreign keys? At least, I will have foreign keys from the
> tables in the specified schema to the tables in "public", because I'm
> thinking that the tables in "public" would be references, while the
> tables residing in the specified schema will be the transactional ones.

That shouldn't be a problem; schemas are just namespaces (with the option
to exclude users through permissions).

Try it and report back if you encounter problems.

Yours,
Laurenz Albe

-- 
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_dump and insert json with copy

[Tom Lane]

basti  writes:
> some tables has json type and show the following error at restore time:

> ERROR:  syntax error at or near "a"
> LINE 1: a:1:{i:0;
> ^

Is that really the very first error reported by the restore run?
It seems much more likely to be a failure cascading from something else.

In any case, you haven't provided nearly enough information for anyone
else to investigate this problem.  Please see
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

regards, tom lane


-- 
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] Schemas and foreign keys

[Andreas Kretschmer]

Am 21.07.2017 um 14:58 schrieb marcelo:
Recently I asked regarding schemas, and received very useful answers. 
I conclude that I can put some tables in one schema and left others in 
the public one.
If my app selects some schema, the backend will found automatically 
the absent tables in "public".

So far, so good.
But what about foreign keys? At least, I will have foreign keys from 
the tables in the specified schema to the tables in "public", because 
I'm thinking that the tables in "public" would be references, while 
the tables residing in the specified schema will be the transactional 
ones.

TIA
Marcelo




that's no problem:

test=# create schema demo1;
CREATE SCHEMA
test=*# create schema demo2;
CREATE SCHEMA
test=*# create table master_table(id int primary key);
CREATE TABLE
test=*# create table demo1.demo_table(id int primary key, master_id int 
references public.master_table);

CREATE TABLE
test=*# create table demo2.demo_table(id int primary key, master_id int 
references public.master_table);

CREATE TABLE


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



--
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_restore misuse or bug?

[Tom Lane]

Jordan Gigov  writes:
> When running pg_restore as the superuser it gives the following error
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 3253; 0 320628
> MATERIALIZED VIEW DATA combined_query_data web_user
> pg_restore: [archiver (db)] could not execute query: ERROR:
> permission denied for relation first_table_in_from_list
> Command was: REFRESH MATERIALIZED VIEW combined_query_data;

What PG version is this?  Can you provide a self-contained test case?

> I see no reason why the superuser would get a "permission denied"
> error.

Matview queries are run as the owner of the matview, so this isn't
as surprising as all that.  But if the matview works in your normal
usage, then pg_dump must be doing something wrong, perhaps emitting
grants in the wrong order.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Schemas and foreign keys

[marcelo]

Recently I asked regarding schemas, and received very useful answers. I 
conclude that I can put some tables in one schema and left others in the 
public one.
If my app selects some schema, the backend will found automatically the 
absent tables in "public".

So far, so good.
But what about foreign keys? At least, I will have foreign keys from the 
tables in the specified schema to the tables in "public", because I'm 
thinking that the tables in "public" would be references, while the 
tables residing in the specified schema will be the transactional ones.

TIA
Marcelo


--
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] ~/.psqlrc file is ignored

[Thom Brown]

On 19 July 2017 at 20:12, vstuart  wrote:
> Hi David: I see what you are saying; sorry for the confusion. This is how
> postgres operates on my system:
>
> [victoria@victoria ~]$ echo $HOME
>   /home/victoria
>
> [victoria@victoria ~]$ which postgres
>   /usr/bin/postgres
>
> [victoria@victoria ~]$ postgres
>   postgres does not know where to find the server configuration file.
>   You must specify the --config-file or -D invocation option or set the
> PGDATA environment variable.
>
> [victoria@victoria ~]$ psql
>   psql: FATAL:  database "victoria" does not exist

By default, psql will attempt to connect to a database named after the
operating system user you are connected as.  It will also use that as
the database user name.  As you don't have a database of the same name
as your user account, you will need to specify it:

psql -d  -U 

Thom


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_dump and insert json with copy

[basti]

Hello,

i had backup my database with the following command

# schema
   PGCLIENTENCODING=utf-8 pg_dump -p 5432 -s -E UTF-8 database | gzip -c
> database_schema.sql.gz
# data
   PGCLIENTENCODING=utf-8 pg_dump -p 5432 -a -E UTF-8 database | gzip -c
> database_data.sql.gz

and try to restore it with

# create db
   PGCLIENTENCODING=utf-8 psql -p 5432 -c "CREATE DATABASE database WITH
ENCODING 'UTF8'"
# schema
   PGCLIENTENCODING=utf-8 zcat database_schema.sql.gz | psql -p 5432
database
# data
   PGCLIENTENCODING=utf-8 zcat database_data.sql.gz | psql -p 5432 database

some tables has json type and show the following error at restore time:

ERROR:  syntax error at or near "a"
LINE 1: a:1:{i:0;
^
ERROR:  syntax error at or near "s"
LINE 1: s:1:"1";
^
ERROR:  syntax error at or near "}"
LINE 1: }s:2:"x4";
^
ERROR:  syntax error at or near "a"
LINE 1: a:1:{^CCancel request sent
ERROR:  syntax error at or near "s"
LINE 1: s:4:"4508";
^
Is there a way to do a clean backup and restore with this json data?

best regards,
basti


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_restore misuse or bug?

[Jordan Gigov]

So we have this database dump created using "pg_dump -Fc ourdb >
file.dump" that has a Materialized View that gets refreshed under
certain conditions by our Java web-app.

When running pg_restore as the superuser it gives the following error
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3253; 0 320628
MATERIALIZED VIEW DATA combined_query_data web_user
pg_restore: [archiver (db)] could not execute query: ERROR:
permission denied for relation first_table_in_from_list
Command was: REFRESH MATERIALIZED VIEW combined_query_data;

If we run it as "pg_restore -d ourdb file.dump" it is treated as a
warning, and the restore is successful, except for the data in the MV.
We have to run the command separately in order to fill the data.
If we run it as "pg_restore -d ourdb -1 file.dump", then it becomes a
fatal error and rolls back the transaction.

I see no reason why the superuser would get a "permission denied"
error. Is this a bug or am I doing something wrong.


-- 
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] Logging at schema level

[Scott Mead]

On Fri, Jul 21, 2017 at 2:11 AM, Nikhil  wrote:

> Schema = tenant. So basically tenant level logging.
>

If each tenant uses a separate user, you could parse this by that user.
You can't separate the logs by user, but, you could use a tool like
pgBadger to parse reports for each individual user (tenant) in the system
and present the logs that way:

From: https://github.com/dalibo/pgbadger

   -u | --dbuser username : only report on entries for the given user.
 

--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

>
> On 21-Jul-2017 11:21 AM, "Andreas Kretschmer" 
> wrote:
>
>> On 21 July 2017 07:10:42 GMT+02:00, Nikhil 
>> wrote:
>> >Hello,
>> >
>> >I am using postgresql schema feature for multi-tenancy. can we get
>> >postgresql logs at schema level. Currently it is for the whole database
>> >server (pg_log)
>> >
>>
>> What do you want to achieve? Logging of data-changes per tenant?
>>
>> Regards, Andreas.
>>
>>
>> --
>> 2ndQuadrant - The PostgreSQL Support Company
>>
>

Re: [GENERAL] Logging at schema level

[Michael Paquier]

On Fri, Jul 21, 2017 at 8:21 AM, John R Pierce  wrote:
> if you have per schema logging, where should that get logged ?
>
> you could implement per DATABASE logging, if you A) add the database name to
> the log_prefix, and B) feed your logs to a program that understands this and
> splits them out to a log file per database.you could also do this on a
> per user basis. but, schema is something very dynamic, its a namespace
> within a database, and queries can touch multiiple schemas.

Personally, I understand that as logging the query N times, once per
schema, if it touches N schemas, making the exercise part of parsing.
I think that it would be possible to use the parser hook to achieve
that actually, as you need extra lookups for WITH clauses and such.
-- 
Michael


-- 
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] Streaming Replication archive_command is really needed?

[Michael Paquier]

On Fri, Jul 21, 2017 at 8:15 AM, Andreas Kretschmer
 wrote:
> Am 21.07.2017 um 08:01 schrieb Michael Paquier:
>> "No" is not completely exact and lacks in details. There are two cases
>> where having an archive is helpful:
>> 1) The standby has disconnected from its primary for a time long
>> enough that WAL segments have been rotated by two completed
>> checkpoints. If that happens, when the standby reconnects it would
>> fail, and you would need to take a new base backup.
>
> you can prevent that using replication slots, but i'm pretty sure you
> (Michael) knows that ;-)
> http://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-replication-phydical-slots/

There is a typo on my URL here. Well that's too late to fix it even if
that's bad style.

>> 2) Backup strategies. Keeping a larger history set of WAL segments is
>> helpful for incremental backups, which is partially the point actually
>> raised upthread about PITR.
>
> Ack, that's right. Using both (streaming and wal-shipping/archiving) will
> make it more robust, and you have (with archiving) the posibility for PITR.
> BUT, you can build a streaming replication without archiving, even you can
> build a continuous backup using only streaming (Barman, streaming only
> mode).

Backup solutions developed by experts on the topic are paths to reliability.
-- 
Michael


-- 
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] Logging at schema level

[Andreas Kretschmer]

Am 21.07.2017 um 08:11 schrieb Nikhil:

Schema = tenant. So basically tenant level logging.

On 21-Jul-2017 11:21 AM, "Andreas Kretschmer" > wrote:


On 21 July 2017 07:10:42 GMT+02:00, Nikhil > wrote:
>Hello,
>
>I am using postgresql schema feature for multi-tenancy. can we get
>postgresql logs at schema level. Currently it is for the whole
database
>server (pg_log)
>

What do you want to achieve? Logging of data-changes per tenant?

Regards, Andreas.


--
2ndQuadrant - The PostgreSQL Support Company



Logging can mean 'logging of errors' or 'logging of data changes'. For 
the latter i'm thinking on logical replication, 
https://blog.2ndquadrant.com/why-logical-replication/


(Please don't top-post)


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



--
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] Logging at schema level

[John R Pierce]

On 7/20/2017 11:11 PM, Nikhil wrote:
Schema = tenant. So basically tenant level logging. 



select  from schema1.table1 join schema2.table2 on  where 
.;



if you have per schema logging, where should that get logged ?

you could implement per DATABASE logging, if you A) add the database 
name to the log_prefix, and B) feed your logs to a program that 
understands this and splits them out to a log file per database.you 
could also do this on a  per user basis. but, schema is something very 
dynamic, its a namespace within a database, and queries can touch 
multiiple schemas.



--
john r pierce, recycling bits in santa cruz



--
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] Streaming Replication archive_command is really needed?

[Andreas Kretschmer]

Am 21.07.2017 um 08:01 schrieb Michael Paquier:

On Thu, Jul 20, 2017 at 10:07 PM, Leonardo M. Ramé  wrote:

El 20/07/17 a las 16:57, Andreas Kretschmer escribió:

On 20 July 2017 21:46:09 GMT+02:00, "Leonardo M. Ramé"
 wrote:

Hi, I wonder if archive_mode=on and archive_command parameters in
postgresql.conf are really needed for streaming replication between two

servers (master-slave).

No.

So, can I just comment those commands and remove the main/archive directory?

"No" is not completely exact and lacks in details. There are two cases
where having an archive is helpful:
1) The standby has disconnected from its primary for a time long
enough that WAL segments have been rotated by two completed
checkpoints. If that happens, when the standby reconnects it would
fail, and you would need to take a new base backup.


you can prevent that using replication slots, but i'm pretty sure you 
(Michael) knows that ;-)
( 
http://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-replication-phydical-slots/ 
)




2) Backup strategies. Keeping a larger history set of WAL segments is
helpful for incremental backups, which is partially the point actually
raised upthread about PITR.


Ack, that's right. Using both (streaming and wal-shipping/archiving) 
will make it more robust, and you have (with archiving) the posibility 
for PITR.
BUT, you can build a streaming replication without archiving, even you 
can build a continuous backup using only streaming (Barman, streaming 
only mode).



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



--
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] Logging at schema level

[Nikhil]

Schema = tenant. So basically tenant level logging.

On 21-Jul-2017 11:21 AM, "Andreas Kretschmer" 
wrote:

> On 21 July 2017 07:10:42 GMT+02:00, Nikhil  wrote:
> >Hello,
> >
> >I am using postgresql schema feature for multi-tenancy. can we get
> >postgresql logs at schema level. Currently it is for the whole database
> >server (pg_log)
> >
>
> What do you want to achieve? Logging of data-changes per tenant?
>
> Regards, Andreas.
>
>
> --
> 2ndQuadrant - The PostgreSQL Support Company
>

Re: [GENERAL] Streaming Replication archive_command is really needed?

[Michael Paquier]

On Thu, Jul 20, 2017 at 10:07 PM, Leonardo M. Ramé  wrote:
> El 20/07/17 a las 16:57, Andreas Kretschmer escribió:
>> On 20 July 2017 21:46:09 GMT+02:00, "Leonardo M. Ramé"
>>  wrote:
>>>
>>> Hi, I wonder if archive_mode=on and archive_command parameters in
>>> postgresql.conf are really needed for streaming replication between two
>>>
>>> servers (master-slave).
>>
>> No.
>
> So, can I just comment those commands and remove the main/archive directory?

"No" is not completely exact and lacks in details. There are two cases
where having an archive is helpful:
1) The standby has disconnected from its primary for a time long
enough that WAL segments have been rotated by two completed
checkpoints. If that happens, when the standby reconnects it would
fail, and you would need to take a new base backup.
2) Backup strategies. Keeping a larger history set of WAL segments is
helpful for incremental backups, which is partially the point actually
raised upthread about PITR.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general