Re: [GENERAL] Feature request: separate logging

[Guillaume Lelarge]

Le 18 nov. 2016 2:03 PM, "otheus uibk"  a écrit :
>
> A glaring weakness in Postgresql for production systems is that the
administrator has no way of controlling what types of logs go where. There
are at least two types of logs: errors and statement logs. (I could also
add: connection, syntax error, query duration, audit). It has becomes
increasingly important in the Linux world, with its over reliance on
systemd, that we admins be able to distinguish between these log outputs.
Systemd wants to control all the daemons and services run on a Linux host.
To do this effectively, it needs to capture the stdout and stderr or
possibly parse the external logfile of the daemon it spawns. The benefits
of systemd's journald subsystem include being able to automatically
identify daemons that are failing to start properly. (The merits of systemd
are beyond the scope of this discussion; it is now the all-but-ubiquitous
standard of linux distributions, and has become nearly intractable).
>
> The Postgresql startup process could greatly benefit from systemd. As it
is now, PGDG distributes postgresql with init scripts which really do very
little to check if postgresql bombed shortly after startup. I have improved
upon that script to do some smart detection of a failed startup, but the
result is having two different files to monitor. On the one hand, I want to
use postgresql's built-in file or CSV logging. On the other, I need to
check the output of the startup process. These logs have different formats,
especially for the smart DBA who wants to customize the log prefix with a
more usable timestamp and other fields. Logging to external files is nice
because postgresql rotates the logs for us automatically. (External log
rotation is problematic and risky because it requires a configuration
reload which may lead to undesirable side-effects. Right?)
>
> One alternative is to capture everything into the local logging system,
or to send all to stdout/stderr and capture this with journald (or runit's
svlogd or something). But then you have the following problem: if I enable
statement-level logging or set log_min_duration_statement=0, the in-memory
journald will quickly be overrun with statement logs. The journald
subsystem will then become useless to the other daemons and subsystems.
>
> One solution is proposed in these forums:
https://www.postgresql.org/message-id/flat/etPan.53397e77.643c9869.1a0%40palos#etPan.53397e77.643c9869.1a0@palos
> While pgbadger has some nice features, it doesn't really solve the
problem of allowing postgresql to be used with systemd+journald.
>
> What I do today is to configure postgresql to write csvlogs.
Stdout/stderr are captured by journald. A custom perl script with the
Text::CSV module and tail -F semantics continuously processes the csvlog
file, ignores query, dml, and detail log lines,  and sends the rest via
syslog() (which journald then handles).
>
> It's not the right way.
>
> I would like to see postgresql to have the ability to
>
>1. Write to a csvlog with one set of selectors
>2. Write to stdout/stderr a different set of selectors (no statement,
no autovacuum, etc) using a purely line-oriented output that
>
>2.1. has the kind of detail contained in the CSV.  Currently, the
log-prefix option does not offer some of the information provided in the
CSV logs. Really, the CSV log should simply be an implementation of the
log-prefix.
>2.2. Collapses multi-lined queries into one line (newlines and
tabs are escaped with backslashes or the x1B character).
>
> Finally, if these changes can be implemented, is it impossible to
backport them to prior versions, say 9.1 and up? If I wrote a patch, under
what conditions would the patch be accepted for inclusion in official
releases of older versions?
>

Only bug fixes are accepted in older releases. What you're proposing isn't
a bug fix.

Re: [GENERAL] pgbench and scaling

[rakeshkumar464]

"Are the TPS numbers per pgbench? If so, then you're getting 
10x490=4900 TPS system wide, or 20*280=5600 TPS system wide. "

Per pgbench.  

Your explanation makes sense. thanks.



--
View this message in context: 
http://postgresql.nabble.com/pgbench-and-scaling-tp5930891p5931131.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] Database migration to RDS issues permissions

[Adrian Klaver]

On 11/19/2016 11:12 AM, Fran ... wrote:
> Hi,
> 
> 
> You were right and I have tried to grant that role to user and I get
> following errors..
> 
> 
> /pg_restore: [archiver (db)] Error while PROCESSING TOC:/
> /pg_restore: [archiver (db)] Error from TOC entry 4335; 2606 151422 FK
> CONSTRAINT worker_id_refs_id_6fd8ce95 owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR:  relation
> "public.worker_status" does not exist/
> /Command was: ALTER TABLE ONLY public.worker_status DROP CONSTRAINT
> worker_id_refs_id_6fd8ce95;/
> 
> /pg_restore: [archiver (db)] Error from TOC entry 4079; 1259 151046
> INDEX id_e owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR:  index
> "id_e" does not exist/
> /Command was: DROP INDEX public.id_e;/
> 
> I run the dump from origin with postgres user.
> 

Alright, I bit the bullet and set up a test Postgres RDS instance. The only way 
I 
could get anything to load was to follow the instructions here:

http://dba.stackexchange.com/questions/66372/moving-a-postgres-database-from-standalone-local-db-to-amazon-rds

and that was only after finding a database that did not have plpythonu 
installed as that is uninstallable.

So I ended up with:

pg_restore -C -d test -h testdb.xxx.rds.amazonaws.com -p 5432 -U 
rds_user --no-owner --no-privileges b_app.out 

I think I will stick with my policy of not using RDS.

-- 
Adrian Klaver
adrian.kla...@aklaver.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] Database migration to RDS issues permissions

[Adrian Klaver]

On 11/19/2016 11:12 AM, Fran ... wrote:
> Hi,
> 
> 
> You were right and I have tried to grant that role to user and I get
> following errors..

GRANT what role to what user?

> 
> 
> /pg_restore: [archiver (db)] Error while PROCESSING TOC:/
> /pg_restore: [archiver (db)] Error from TOC entry 4335; 2606 151422 FK
> CONSTRAINT worker_id_refs_id_6fd8ce95 owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR:  relation
> "public.worker_status" does not exist/
> /Command was: ALTER TABLE ONLY public.worker_status DROP CONSTRAINT
> worker_id_refs_id_6fd8ce95;/
> 
> /pg_restore: [archiver (db)] Error from TOC entry 4079; 1259 151046
> INDEX id_e owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR:  index
> "id_e" does not exist/
> /Command was: DROP INDEX public.id_e;/
> 
> I run the dump from origin with postgres user.
> 

Pretty sure the issue is less where it is coming from then where it is going. 
RDS has constraints on what a user can do. As I said before I do not use it, 
so I cannot be of much help other then to point you at the docs:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts

Using the rds_superuser Role


Seems to be you need to use the above role to do your restore.

-- 
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] Strict min and max aggregate functions

[Jeff Janes]

I need "strict" MIN and MAX aggregate functions, meaning they return NULL
upon any NULL input, and behave like the built-in aggregates if none of the
input values are NULL.

This doesn't seem like an outlandish thing to want, and I'm surprised I
can't find other discussion of it.  Perhaps because none of the words here
are very effective as search terms as they are so individually common.

I've hit upon a solution that works, but it is both ugly and slow (about 50
fold slower than the built-ins; for my current purpose this is not a big
problem but I would love it to be faster if that could be done easily).

So here is my approach.  Any suggestions to improve it?  Or are there
better canned solutions I've failed to find?


-- If no values have been delivered to the aggregate, the internal state is
the
-- NULL array.  If a null values has been delivered, the internal status is
an
-- array with one element, which is NULL.  Otherwise, it is an array with
one element,
-- the least/greatest seen so far.

CREATE OR REPLACE FUNCTION strict_min_agg (anyarray,anyelement )
RETURNS anyarray LANGUAGE sql IMMUTABLE AS $$
SELECT CASE
WHEN $1 IS NULL THEN ARRAY[$2]
WHEN $1[1] IS NULL THEN $1
WHEN $2 IS NULL THEN ARRAY[$2] -- use $2 not NULL to preserve
type
ELSE ARRAY[least($1[1],$2)] END ;
$$;


CREATE OR REPLACE FUNCTION strict_min_final (anyarray)
RETURNS anyelement LANGUAGE sql IMMUTABLE AS $$
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE $1[1] END ;
$$;

CREATE AGGREGATE strict_min (x anyelement) (
sfunc = strict_min_agg,
stype = anyarray,
finalfunc = strict_min_final
);


Cheers,

Jeff

Re: [GENERAL] Partial update on an postgres upsert violates constraint

[Tom Lane]

Adrian Klaver  writes:
> ... So looks like constraints are checked before you get to the ON CONFLICT 
> section.

Right.  ON CONFLICT is a means for dealing with duplicate-key errors in
the specified (or inferred) unique index.  It is *not* an all-purpose
error catcher.  In the case at hand, the given INSERT request fails due
to not-null constraints that are unrelated to what the ON CONFLICT clause
tests for.

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] Partial update on an postgres upsert violates constraint

[Adrian Klaver]

On 11/19/2016 11:33 AM, Kim Rose Carlsen wrote:
>> AFAIK, EXCLUDED is only available in a trigger function:
> 
>>
>> https://www.postgresql.org/docs/9.5/static/trigger-definition.html
>>
>> You are using EXCLUDED in a regular function so it would not be found.
>> 
>> Can you also show the failure for your alternate method?
> 
> From the manual
> https://www.postgresql.org/docs/9.5/static/sql-insert.html
> 
> "
> conflict_action
> conflict_action specifies an alternative ON CONFLICT action. It can be
> either DO NOTHING, or a DO UPDATE clause specifying the exact details of
> the UPDATE action to be performed in case of a conflict. The SET and
> WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row
> using the table's name (or an alias), and to rows proposed for insertion
> using the special excluded table. SELECT privilege is required on any
> column in the target table where corresponding excluded columns are read.
> "
> 


Oops, my mistake. I should have spent more time on the examples.

Changing the function to;

CREATE OR REPLACE FUNCTION public.upsert_job(job jsonb)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
INSERT INTO jobs AS origin VALUES(
(job->>'id')::INTEGER,
COALESCE(job->>'employee_name'::TEXT, 'test_name'),
COALESCE(job->>'address'::TEXT, 'test_address'),
job->>'phone_number'::TEXT
) ON CONFLICT (id) DO UPDATE SET
employee_name = COALESCE(EXCLUDED.employee_name, origin.employee_name),
address = COALESCE(EXCLUDED.address, origin.address),
phone_number = COALESCE(EXCLUDED.phone_number, origin.phone_number);
END;
$function$
;

makes it work. So looks like constraints are checked before you get to the ON 
CONFLICT section.


-- 
Adrian Klaver
adrian.kla...@aklaver.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] Partial update on an postgres upsert violates constraint

[Kim Rose Carlsen]

> AFAIK, EXCLUDED is only available in a trigger function:

>
> https://www.postgresql.org/docs/9.5/static/trigger-definition.html
>
> You are using EXCLUDED in a regular function so it would not be found.
>
> Can you also show the failure for your alternate method?

>From the manual
https://www.postgresql.org/docs/9.5/static/sql-insert.html

"
conflict_action
conflict_action specifies an alternative ON CONFLICT action. It can be either 
DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE 
action to be performed in case of a conflict. The SET and WHERE clauses in ON 
CONFLICT DO UPDATE have access to the existing row using the table's name (or 
an alias), and to rows proposed for insertion using the special excluded table. 
SELECT privilege is required on any column in the target table where 
corresponding excluded columns are read.
"

Re: [GENERAL] Database migration to RDS issues permissions

[Fran ...]

Hi,


You were right and I have tried to grant that role to user and I get following 
errors..


pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4335; 2606 151422 FK 
CONSTRAINT worker_id_refs_id_6fd8ce95 owneruser
pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
"public.worker_status" does not exist
Command was: ALTER TABLE ONLY public.worker_status DROP CONSTRAINT 
worker_id_refs_id_6fd8ce95;

pg_restore: [archiver (db)] Error from TOC entry 4079; 1259 151046 INDEX id_e 
owneruser
pg_restore: [archiver (db)] could not execute query: ERROR:  index "id_e" does 
not exist
Command was: DROP INDEX public.id_e;


I run the dump from origin with postgres user.


De: Adrian Klaver 
Enviado: sábado, 19 de noviembre de 2016 18:41
Para: Fran ...; pgsql-general@postgresql.org; t...@sss.pgh.pa.us
Asunto: Re: [GENERAL] Database migration to RDS issues permissions

On 11/19/2016 09:33 AM, Fran ... wrote:
> Hi,
>
>
> I run "pg_dumpall" command and there are the permissions por the user:
>
>
> /CREATE ROLE dlapuser;/
> /ALTER ROLE dlapuser WITH *SUPERUSER* INHERIT NOCREATEROLE NOCREATEDB
> LOGIN NOREPLICATION PASSWORD 'md5XXafac';/
>
> I think I would solve the problem granting "superuser" permission but
> this is not possible in RDS.

I don't use RDS, but from what I gather the above is not strictly true:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html
Importing Data into PostgreSQL on Amazon RDS - Amazon 
...
docs.aws.amazon.com
If you have an existing PostgreSQL deployment that you want to move to Amazon 
RDS, the complexity of your task depends on the size of your database and the 
...




>
>
> I also saw the first errors and they are weird.
>

Indications that you are not running the restore as a user with
sufficient privileges.

Is the database you are dumping from an RDS instance or a regular
Postgres database?

>
> @Adrian Klaver  what others
> permissions do you suggest?
>
> Origin and target are the same version of course. PostgreSQL 9.4.1
>
> Thanks in advance.
>
> 
> *De:* Adrian Klaver 
> *Enviado:* sábado, 19 de noviembre de 2016 18:24
> *Para:* Fran ...; pgsql-general@postgresql.org
> *Asunto:* Re: [GENERAL] Database migration to RDS issues permissions
>
> On 11/19/2016 07:21 AM, Fran ... wrote:
>> Hi Adrian,
>>
>>
>> these are some of them:
>>
>>
>> /pg_restore: [archiver (db)] Error from TOC entry 4997; 0 0 SEQUENCE SET
>> account_id_seq owneruser/
>> /pg_restore: [archiver (db)] could not execute query: ERROR:  permission
>> denied for sequence account_id_seq/
>> /Command was: SELECT pg_catalog.setval('account_id_seq', 26738, true);/
>> /
>> /
>>
>> /pg_restore: [archiver (db)] Error from TOC entry 4548; 0 106491 TABLE
>> DATA account owneruser/
>> /pg_restore: [archiver (db)] could not execute query: ERROR:  permission
>> denied for relation account/
>> /Command was: COPY account (id, user_id, test, picture, status) FROM
>> stdin;/
>> /
>> /
>>
>> /pg_restore: [archiver (db)] Error from TOC entry 3763; 2604 1179420
>> DEFAULT id owneruser/
>> /pg_restore: [archiver (db)] could not execute query: ERROR:  must be
>> owner of relation trix_venue/
>> /Command was: ALTER TABLE ONLY venue ALTER COLUMN id SET DEFAULT
>> eval('venue_id_seq'::regclass);/
>>
>> Thanks in advance.
>
> In addition to what Tom said:
>
> create database database;
> grant all privileges on database to ownerdatabase;
>
> is probably not doing what you think it is or want.
>
> A GRANT on a database only grants connect privileges and the ability to
> create schemas in the database. It does not allow creating of objects
> within the schema. For more details see:
>
> https://www.postgresql.org/docs/9.5/static/sql-grant.html
> PostgreSQL: Documentation: 9.5: GRANT
> 
> www.postgresql.org
> GRANT on Database Objects. This variant of the GRANT command gives
> specific privileges on a database object to one or more roles. These
> privileges are added to those ...
>
>
>
>
>
>>
>>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: [GENERAL] Database migration to RDS issues permissions

[Adrian Klaver]

On 11/19/2016 09:33 AM, Fran ... wrote:

Hi,


I run "pg_dumpall" command and there are the permissions por the user:


/CREATE ROLE dlapuser;/
/ALTER ROLE dlapuser WITH *SUPERUSER* INHERIT NOCREATEROLE NOCREATEDB
LOGIN NOREPLICATION PASSWORD 'md5XXafac';/

I think I would solve the problem granting "superuser" permission but
this is not possible in RDS.


I don't use RDS, but from what I gather the above is not strictly true:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html




I also saw the first errors and they are weird.



Indications that you are not running the restore as a user with 
sufficient privileges.


Is the database you are dumping from an RDS instance or a regular 
Postgres database?




@Adrian Klaver  what others
permissions do you suggest?

Origin and target are the same version of course. PostgreSQL 9.4.1

Thanks in advance.


*De:* Adrian Klaver 
*Enviado:* sábado, 19 de noviembre de 2016 18:24
*Para:* Fran ...; pgsql-general@postgresql.org
*Asunto:* Re: [GENERAL] Database migration to RDS issues permissions

On 11/19/2016 07:21 AM, Fran ... wrote:

Hi Adrian,


these are some of them:


/pg_restore: [archiver (db)] Error from TOC entry 4997; 0 0 SEQUENCE SET
account_id_seq owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR:  permission
denied for sequence account_id_seq/
/Command was: SELECT pg_catalog.setval('account_id_seq', 26738, true);/
/
/

/pg_restore: [archiver (db)] Error from TOC entry 4548; 0 106491 TABLE
DATA account owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR:  permission
denied for relation account/
/Command was: COPY account (id, user_id, test, picture, status) FROM
stdin;/
/
/

/pg_restore: [archiver (db)] Error from TOC entry 3763; 2604 1179420
DEFAULT id owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR:  must be
owner of relation trix_venue/
/Command was: ALTER TABLE ONLY venue ALTER COLUMN id SET DEFAULT
eval('venue_id_seq'::regclass);/

Thanks in advance.


In addition to what Tom said:

create database database;
grant all privileges on database to ownerdatabase;

is probably not doing what you think it is or want.

A GRANT on a database only grants connect privileges and the ability to
create schemas in the database. It does not allow creating of objects
within the schema. For more details see:

https://www.postgresql.org/docs/9.5/static/sql-grant.html
PostgreSQL: Documentation: 9.5: GRANT

www.postgresql.org
GRANT on Database Objects. This variant of the GRANT command gives
specific privileges on a database object to one or more roles. These
privileges are added to those ...












--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.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] Database migration to RDS issues permissions

[Tom Lane]

"Fran ..."  writes:
> I think I would solve the problem granting "superuser" permission but this is 
> not possible in RDS.

It looks like your other errors are also due to doing the restore as
a non-superuser.  Not sure if you have any good alternatives here ---
you could just ignore the errors relating to plpgsql, but if you have
C-language functions that you need to migrate, there is no way to
install those without superuser privileges.

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] Database migration to RDS issues permissions

[Fran ...]

Hi,


I run "pg_dumpall" command and there are the permissions por the user:


CREATE ROLE dlapuser;
ALTER ROLE dlapuser WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN 
NOREPLICATION PASSWORD 'md5XXafac';

I think I would solve the problem granting "superuser" permission but this is 
not possible in RDS.


I also saw the first errors and they are weird.


pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4751; 0 0 COMMENT EXTENSION 
plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of 
extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

pg_restore: [archiver (db)] Error from TOC entry 4752; 0 0 COMMENT EXTENSION 
pg_stat_statements
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of 
extension pg_stat_statements
Command was: COMMENT ON EXTENSION pg_stat_statements IS 'track execution 
statistics of all SQL statements executed';

pg_restore: [archiver (db)] Error from TOC entry 4753; 0 0 COMMENT EXTENSION 
postgres_fdw
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of 
extension postgres_fdw
Command was: COMMENT ON EXTENSION postgres_fdw IS 'foreign-data wrapper for 
remote PostgreSQL servers';

pg_restore: [archiver (db)] Error from TOC entry 408; 1255 563407 FUNCTION 
cksum2(text) postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied 
for language c
Command was: CREATE FUNCTION cksum2(text) RETURNS smallint
LANGUAGE c
AS '$libdir/pgc_checksum', 'text_checksum2';


pg_restore: [archiver (db)] could not execute query: ERROR:  function 
public.cksum2(text) does not exist
Command was: ALTER FUNCTION public.cksum2(text) OWNER TO postgres;

pg_restore: [archiver (db)] Error from TOC entry 411; 1255 563408 FUNCTION 
cksum4(text) postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied 
for language c
Command was: CREATE FUNCTION cksum4(text) RETURNS integer
LANGUAGE c
AS '$libdir/pgc_checksum', 'text_checksum4';

pg_restore: [archiver (db)] could not execute query: ERROR:  function 
public.cksum4(text) does not exist
Command was: ALTER FUNCTION public.cksum4(text) OWNER TO postgres;

@Adrian Klaver what others permissions do you 
suggest?

Origin and target are the same version of course. PostgreSQL 9.4.1

Thanks in advance.


De: Adrian Klaver 
Enviado: sábado, 19 de noviembre de 2016 18:24
Para: Fran ...; pgsql-general@postgresql.org
Asunto: Re: [GENERAL] Database migration to RDS issues permissions

On 11/19/2016 07:21 AM, Fran ... wrote:
> Hi Adrian,
>
>
> these are some of them:
>
>
> /pg_restore: [archiver (db)] Error from TOC entry 4997; 0 0 SEQUENCE SET
> account_id_seq owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR:  permission
> denied for sequence account_id_seq/
> /Command was: SELECT pg_catalog.setval('account_id_seq', 26738, true);/
> /
> /
>
> /pg_restore: [archiver (db)] Error from TOC entry 4548; 0 106491 TABLE
> DATA account owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR:  permission
> denied for relation account/
> /Command was: COPY account (id, user_id, test, picture, status) FROM
> stdin;/
> /
> /
>
> /pg_restore: [archiver (db)] Error from TOC entry 3763; 2604 1179420
> DEFAULT id owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR:  must be
> owner of relation trix_venue/
> /Command was: ALTER TABLE ONLY venue ALTER COLUMN id SET DEFAULT
> eval('venue_id_seq'::regclass);/
>
> Thanks in advance.

In addition to what Tom said:

create database database;
grant all privileges on database to ownerdatabase;

is probably not doing what you think it is or want.

A GRANT on a database only grants connect privileges and the ability to
create schemas in the database. It does not allow creating of objects
within the schema. For more details see:

https://www.postgresql.org/docs/9.5/static/sql-grant.html
PostgreSQL: Documentation: 9.5: 
GRANT
www.postgresql.org
GRANT on Database Objects. This variant of the GRANT command gives specific 
privileges on a database object to one or more roles. These privileges are 
added to those ...





>
>



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: [GENERAL] Database migration to RDS issues permissions

[Adrian Klaver]

On 11/19/2016 07:21 AM, Fran ... wrote:

Hi Adrian,


these are some of them:


/pg_restore: [archiver (db)] Error from TOC entry 4997; 0 0 SEQUENCE SET
account_id_seq owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR:  permission
denied for sequence account_id_seq/
/Command was: SELECT pg_catalog.setval('account_id_seq', 26738, true);/
/
/

/pg_restore: [archiver (db)] Error from TOC entry 4548; 0 106491 TABLE
DATA account owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR:  permission
denied for relation account/
/Command was: COPY account (id, user_id, test, picture, status) FROM
stdin;/
/
/

/pg_restore: [archiver (db)] Error from TOC entry 3763; 2604 1179420
DEFAULT id owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR:  must be
owner of relation trix_venue/
/Command was: ALTER TABLE ONLY venue ALTER COLUMN id SET DEFAULT
eval('venue_id_seq'::regclass);/

Thanks in advance.


In addition to what Tom said:

create database database;
grant all privileges on database to ownerdatabase;

is probably not doing what you think it is or want.

A GRANT on a database only grants connect privileges and the ability to 
create schemas in the database. It does not allow creating of objects 
within the schema. For more details see:


https://www.postgresql.org/docs/9.5/static/sql-grant.html









--
Adrian Klaver
adrian.kla...@aklaver.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] Database migration to RDS issues permissions

[Tom Lane]

"Fran ..."  writes:
> these are some of them:

These look to be cascading damage from some earlier failure.  I'd advise
looking at the first one or two errors and solving them, then repeat
as necessary.

In general, though, pg_dump of a single database is not a complete
representation of where you were: it lacks any information about
global objects (roles and tablespaces).  I suspect your problems
ultimately trace back to not having created the right roles in
the target installation before starting the restore.  You might
find "pg_dumpall -g" to be helpful.

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] [HACKERS] How to change order sort of table in HashJoin

[Tom Lane]

Man Trieu  writes:
> As in the example below, i think the plan which hash table is created on
> testtbl2 (the fewer tuples) should be choosen.

The planner usually prefers to hash on the table that has a flatter
MCV histogram, since a hash table with many key collisions will be
inefficient.  You might find it illuminating to read the comments around
estimate_hash_bucketsize().

In general, given a hashtable that fits in memory and light bucket
loading, a hash join is more or less O(M) + O(N); it doesn't matter
so much whether the larger table is on the inside.  It does matter if
the table gets big enough to force batching of the join, but that's
not happening in your example (at least not the first one; it's unclear
to me why it did happen in the second one).  The key thing that will
drive the choice, then, is avoiding a skewed bucket distribution that
causes lots of comparisons for common values.

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] Database migration to RDS issues permissions

[Fran ...]

Hi Adrian,


these are some of them:


pg_restore: [archiver (db)] Error from TOC entry 4997; 0 0 SEQUENCE SET 
account_id_seq owneruser
pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied 
for sequence account_id_seq
Command was: SELECT pg_catalog.setval('account_id_seq', 26738, true);


pg_restore: [archiver (db)] Error from TOC entry 4548; 0 106491 TABLE DATA 
account owneruser
pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied 
for relation account
Command was: COPY account (id, user_id, test, picture, status) FROM stdin;


pg_restore: [archiver (db)] Error from TOC entry 3763; 2604 1179420 DEFAULT id 
owneruser
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of 
relation trix_venue
Command was: ALTER TABLE ONLY venue ALTER COLUMN id SET DEFAULT 
eval('venue_id_seq'::regclass);

Thanks in advance.


De: Adrian Klaver 
Enviado: sábado, 19 de noviembre de 2016 15:41
Para: Fran ...; pgsql-general@postgresql.org
Asunto: Re: [GENERAL] Database migration to RDS issues permissions

On 11/19/2016 05:21 AM, Fran ... wrote:
> Hi,
>
>
> I have to migrate a production database to RDS. This is the size and info:
>
>
>  database  | owneruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> =Tc/owneruser+| 32 GB   | pg_default |
>
>
> Origin database:
>
> 1 database
>
> 1 owneruser with superuser permission
>
>
> Backup archived size is 2G and it takes less than a minute.
>
>
> I am trying to running the following steps in a DEV environment and I am
> having problems with destination permissions.
>
>
> 1º-Creating RDS instance
>
> Done and I can connect to.
>
>
> 2º-Making backup:
>
> pg_dump -F c database > backup_db.dump
>
> 3º-Creating user,database and grant permissions in RDS.
> Create database database;
> CREATE USER owneruser WITH PASSWORD 'owneruser';
> create database database;
> grant all privileges on database to ownerdatabase;
>
> 4º-Restoring backup
> pg_restore -d database -h hostname -U postgres -F c -f log_file.log
> backup.db.dump
>
> While restoring is working it prints a lot of permissions errors.

The permissions errors are ?

A sampling will suffice for now.

>
> It's being hard to find "how to" and documentations about right permissions.
>
> Regards.
>


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: [GENERAL] Partial update on an postgres upsert violates constraint

[Adrian Klaver]

On 11/17/2016 10:13 PM, Andreas Terrius wrote:

Hi,
Basically I wanted to do a partial update inside pg (9.5), but it seems
that a partial update fails when not all of constraint is fulfilled
(such as the not null constraint)

Below are the sql queries I used,

|CREATETABLEjobs (id integer PRIMARYKEY,employee_name TEXT
NOTNULL,address TEXT NOTNULL,phone_number TEXT );CREATEORREPLACE
FUNCTIONupsert_job(job JSONB)RETURNS VOID AS$$BEGININSERTINTOjobs
ASorigin
VALUES((job->>'id')::INTEGER,job->>'employee_name'::TEXT,job->>'address'::TEXT,job->>'phone_number'::TEXT
)ONCONFLICT (id)DO UPDATESETemployee_name
=COALESCE(EXCLUDED.employee_name,origin.employee_name),address
=COALESCE(EXCLUDED.address,origin.address),phone_number
=COALESCE(EXCLUDED.phone_number,origin.phone_number);END;$$LANGUAGE
PLPGSQL SECURITY DEFINER;--Full insert (OK)SELECTupsert_job('{"id" : 1,
"employee_name" : "AAA", "address" : "City, x street no.y",
"phone_number" : "123456789"}'::jsonb);--Partial update that fulfills
constraint (Ok)SELECTupsert_job('{"id" : 1, "employee_name" : "BBB",
"address" : "City, x street no.y"}'::jsonb);--Partial update that
doesn't fulfill constraint (FAILS)SELECTupsert_job('{"id" : 1,
"phone_number" : "12345"}'::jsonb);--ERROR: null value in column
"employee_name" violates not-null constraint--DETAIL: Failing row
contains (1, null, null, 12345).|

I also tried explicitly stating the columns that I wanted to insert, and
it also fails. How do I go around doing this ?


AFAIK, EXCLUDED is only available in a trigger function:

https://www.postgresql.org/docs/9.5/static/trigger-definition.html

You are using EXCLUDED in a regular function so it would not be found.

Can you also show the failure for your alternate method?



Thank you



--
Adrian Klaver
adrian.kla...@aklaver.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] How to change order sort of table in HashJoin

[Melvin Davidson]

On Sat, Nov 19, 2016 at 12:46 AM, Man Trieu  wrote:

> Hi Experts,
>
> As in the example below, i think the plan which hash table is created on
> testtbl2 (the fewer tuples) should be choosen.
> Because creating of hash table should faster in testtbl2. But it did not.
>
> I have tried to change the ordering of table by tuning parameter even if
> using pg_hint_plan but not success.
>
> Why does planner do not choose the plan which hash table is created on
> testtbl2 (which can take less time)?
> And how to change the order?
>
> # I also confirm planner info by rebuild postgresql but not found related
> usefull info about hash table
>
> ---
> postgres=# create table testtbl1(id integer, c1 text, c2 text, c3 text,
> primary key (c1,c2,c3));
> CREATE TABLE
> postgres=# create table testtbl2(id integer, c1 text, c2 text, c3 text,
> primary key (c1,c2,c3));
> CREATE TABLE
> postgres=# insert into testtbl1 select generate_series(1,100),
> random()::text,random()::text,random()::text;
> INSERT 0 100
> postgres=# insert into testtbl2 select * from testtbl1 where id%7 = 0;
> INSERT 0 142857
>
> postgres=# explain analyze select * from testtbl1 inner join testtbl2
> using(c1,c2,c3);
>QUERY PLAN
> 
> -
>  Hash Join  (cost=38775.00..47171.72 rows=1 width=59) (actual
> time=1120.824..1506.236 rows=142857 loops=1)
>Hash Cond: ((testtbl2.c1 = testtbl1.c1) AND (testtbl2.c2 = testtbl1.c2)
> AND (testtbl2.c3 = testtbl1.c3))
>->  Seq Scan on testtbl2  (cost=0.00..3039.57 rows=142857 width=56)
> (actual time=0.008..27.964 rows=142857 loops=1)
>->  Hash  (cost=21275.00..21275.00 rows=100 width=55) (actual
> time=1120.687..1120.687 rows=100 loops=1)
>  Buckets: 131072  Batches: 1  Memory Usage: 89713kB
>  ->  Seq Scan on testtbl1  (cost=0.00..21275.00 rows=100
> width=55) (actual time=0.035..458.522 rows=100 loops=1)
>  Planning time: 0.922 ms
>  Execution time: 1521.258 ms
> (8 rows)
>
> postgres=# set pg_hint_plan.enable_hint to on;
> SET
> postgres=# /*+
> postgres*# HashJoin(testtbl1 testtbl2)
> postgres*# Leading(testtbl1 testtbl2)
> postgres*# */
> postgres-# explain analyze select * from testtbl1 inner join testtbl2
> using(c1,c2,c3);
>QUERY PLAN
> 
> -
>  Hash Join  (cost=48541.00..67352.86 rows=1 width=59) (actual
> time=1220.625..1799.709 rows=142857 loops=1)
>Hash Cond: ((testtbl2.c1 = testtbl1.c1) AND (testtbl2.c2 = testtbl1.c2)
> AND (testtbl2.c3 = testtbl1.c3))
>->  Seq Scan on testtbl2  (cost=0.00..3039.57 rows=142857 width=56)
> (actual time=0.011..58.649 rows=142857 loops=1)
>->  Hash  (cost=21275.00..21275.00 rows=100 width=55) (actual
> time=1219.295..1219.295 rows=100 loops=1)
>  Buckets: 8192  Batches: 32  Memory Usage: 2851kB
>  ->  Seq Scan on testtbl1  (cost=0.00..21275.00 rows=100
> width=55) (actual time=0.021..397.583 rows=100 loops=1)
>  Planning time: 3.971 ms
>  Execution time: 1807.710 ms
> (8 rows)
>
> postgres=#
> ---
>
>
> Thanks and best regard!
>




*AFAIK, the only way to change a sort order is to use the ORDER BY clause
in the SELECT.https://www.postgresql.org/docs/9.4/static/sql-select.html
"8. If the
ORDER BY clause is specified, the returned rows are sorted in the specified
order. If ORDER BY is not given, the rows are returned in whatever order
the system finds fastest to produce."*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Database migration to RDS issues permissions

[Adrian Klaver]

On 11/19/2016 05:21 AM, Fran ... wrote:

Hi,


I have to migrate a production database to RDS. This is the size and info:


 database  | owneruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=Tc/owneruser+| 32 GB   | pg_default |


Origin database:

1 database

1 owneruser with superuser permission


Backup archived size is 2G and it takes less than a minute.


I am trying to running the following steps in a DEV environment and I am
having problems with destination permissions.


1º-Creating RDS instance

Done and I can connect to.


2º-Making backup:

pg_dump -F c database > backup_db.dump

3º-Creating user,database and grant permissions in RDS.
Create database database;
CREATE USER owneruser WITH PASSWORD 'owneruser';
create database database;
grant all privileges on database to ownerdatabase;

4º-Restoring backup
pg_restore -d database -h hostname -U postgres -F c -f log_file.log
backup.db.dump

While restoring is working it prints a lot of permissions errors.


The permissions errors are ?

A sampling will suffice for now.



It's being hard to find "how to" and documentations about right permissions.

Regards.




--
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] Database migration to RDS issues permissions

[Fran ...]

Hi,


I have to migrate a production database to RDS. This is the size and info:


 database  | owneruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
=Tc/owneruser+| 32 GB   | pg_default |


Origin database:

1 database

1 owneruser with superuser permission


Backup archived size is 2G and it takes less than a minute.


I am trying to running the following steps in a DEV environment and I am having 
problems with destination permissions.


1º-Creating RDS instance

Done and I can connect to.


2º-Making backup:

pg_dump -F c database > backup_db.dump

3º-Creating user,database and grant permissions in RDS.
Create database database;
CREATE USER owneruser WITH PASSWORD 'owneruser';
create database database;
grant all privileges on database to ownerdatabase;

4º-Restoring backup
pg_restore -d database -h hostname -U postgres -F c -f log_file.log 
backup.db.dump

While restoring is working it prints a lot of permissions errors.

It's being hard to find "how to" and documentations about right permissions.

Regards.