[GENERAL] postgresql service is working but showing status failed

[devangn]

Everything looks and works as it should, just i am not getting the status
opensips service as active (running) instead i am getting active (exited),
what am i missing here?
# netstat  -pan|grep 5432
tcp0  0 0.0.0.0:54320.0.0.0:*   LISTEN 
589/postgres

# /etc/init.d/postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled)
   Active: active (exited) since Thu 2013-11-14 22:14:42 WAST; 2 years 10
months ago
 Main PID: 606 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service

Nov 14 22:14:42 db01 systemd[1]: Started PostgreSQL RDBMS.

--> below is the content of file /lib/systemd/system/postgresql.service
# systemd service for managing all PostgreSQL clusters on the system. This
# service is actually a systemd target, but we are using a service since
# targets cannot be reloaded.

[Unit]
Description=PostgreSQL RDBMS

[Service]
Type=oneshot
#ExecStartPre=/usr/bin
ExecStart= /bin/true
ExecReload=/bin/true
ExecStop=  /bin/true
RemainAfterExit=on

[Install]
WantedBy=multi-user.target




--
View this message in context: 
http://postgresql.nabble.com/postgresql-service-is-working-but-showing-status-failed-tp5924295.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

[GENERAL] Make psql print number values right-aligned with locale group separator?

[David G. Johnston]

Take this as either a request for a pointer to how to accomplish $subject
or a feature request.

psql nicely prints number values right-aligned - but otherwise applies only
a single decimal separator.

Using to_char() to get the proper formatting is annoying and has downsides
as well (mixing presentation and logic and keeping the header length
aligned).

Am I overlooking a feature here or is just not something anyone has been
annoyed enough to do anything about?

Thanks!

David J.

Re: [GENERAL] isnull() function in pgAdmin3

[David G. Johnston]

On Mon, Oct 3, 2016 at 6:39 AM, dudedoe01  wrote:

> What is the most feasible way to emulate the below MySQL function into
> postgreSQL. Since the isnull() function is no longer supported in 9.6
> version. I have tried every trick in the hat to get the desired results.
> Still 'RPG INV' doesn't show only the other two then options show up.
>
> (case
> when
> ((`s`.`Funding_Date` = '')
> and (isnull(`s`.`Actual_Close_Date`)
> or (`s`.`Actual_Close_Date` = '')))
> then
> 'RPG_INV'
> when
> ((isnull(`s`.`Funding_Date`)
> or (`s`.`Funding_Date` <> ''))
> and ((`s`.`Actual_Close_Date` = '')
> or isnull(`s`.`Actual_Close_Date`)))
> then
> 'Builder_Inventory'
> else 'Owner_Inventory'
> end) AS `Lot_Status`
>
>
​If you include something like the following people are going to be more
willing to provide help and better able to provide good help.

WITH dt (funding_date, actual_date) AS (
VALUES (null, null), ('X', 'X'),
   (null, ''), (null, 'X'),
   ('', null), ('X', null)
)
SELECT funding_date, actual_date,
   CASE WHEN funding_date IS NULL
 AND actual_date IS NULL
THEN 'Both Null'
ELSE 'write more WHEN statements...'
END
FROM dt
​;

That's a self-contained example and it does wonders for problem solving.

David J.
​

[GENERAL] Re: [GENERAL] Understanding “max_wal_size” and “min_wal_size” parameters default values from postgresql.conf file

[otar shavadze]

Thank you very much

On Mon, Oct 3, 2016 at 11:46 PM, Tom Lane  wrote:

> otar shavadze  writes:
> > name |  setting | unit--
> > max_wal_size | 64   |
> > min_wal_size | 5|
>
> > I have 2 questions:
>
> > 1) Why these values doesn't match default values, which are shown in
> docs?
> > I never changed config settings at all.
>
> They do match the defaults.
>
> > 2) Why unit column is empty/NULL for these parameters? What means 64 and
> 5
> > values in this case? MB? GB? or what?
>
> The problem seems to be that somebody missed out adding GUC_UNIT_XSEGS
> to the switch in GetConfigOptionByNum.  It should be showing you
> something like "16MB" in the unit column, I think.
>
> regards, tom lane
>

Re: [GENERAL] isnull() function in pgAdmin3

[Raymond O'Donnell]

On 03/10/16 14:39, dudedoe01 wrote:

What is the most feasible way to emulate the below MySQL function into
postgreSQL. Since the isnull() function is no longer supported in 9.6
version. I have tried every trick in the hat to get the desired results.
Still 'RPG INV' doesn't show only the other two then options show up.

(case
when
((`s`.`Funding_Date` = '')
and (isnull(`s`.`Actual_Close_Date`)
or (`s`.`Actual_Close_Date` = '')))




Maybe I missed something earlier in the thread (only tuned in now), but 
what's wrong with


   ... and s.actual_close_date is null 

?

Ray.





--
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] isnull() function in pgAdmin3

[Adrian Klaver]

On 10/03/2016 06:39 AM, dudedoe01 wrote:

What is the most feasible way to emulate the below MySQL function into
postgreSQL. Since the isnull() function is no longer supported in 9.6


One more time, Postgres does not have an isnull() function in any 
version AFAIK. You need to use IS NULL:


https://www.postgresql.org/message-id/MWHPR07MB28777547180DC028EF812E10DACC0%40MWHPR07MB2877.namprd07.prod.outlook.com

https://www.postgresql.org/message-id/80becd5e-2fcf-5660-574b-82bcb040e18a%40aklaver.com

https://www.postgresql.org/message-id/CACjxUsOkFgiGKRjmhWonE3yfiPpsrn9dsmHW%2B9KaZSh9RWB_ow%40mail.gmail.com


version. I have tried every trick in the hat to get the desired results.
Still 'RPG INV' doesn't show only the other two then options show up.

(case
when
((`s`.`Funding_Date` = '')
and (isnull(`s`.`Actual_Close_Date`)
or (`s`.`Actual_Close_Date` = '')))
then
'RPG_INV'
when
((isnull(`s`.`Funding_Date`)
or (`s`.`Funding_Date` <> ''))
and ((`s`.`Actual_Close_Date` = '')
or isnull(`s`.`Actual_Close_Date`)))
then
'Builder_Inventory'
else 'Owner_Inventory'
end) AS `Lot_Status`


If I am following correctly:

(
CASE
WHEN
(`s`.`Funding_Date` = '')
AND
(
(`s`.`Actual_Close_Date` IS NULL)
OR
(`s`.`Actual_Close_Date` = '')
)
THEN
'RPG_INV'
WHEN
(
(`s`.`Funding_Date` IS NULL)
OR
(`s`.`Funding_Date` <> '')
)

AND
(
(`s`.`Actual_Close_Date` IS NULL)
OR
(`s`.`Actual_Close_Date` = '')
)
THEN
'Builder_Inventory'
ELSE
'Owner_Inventory'
END
)
AS
`Lot_Status`




--
View this message in context: 
http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5924161.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] isnull() function in pgAdmin3

[Ken Tanzer]

On Mon, Oct 3, 2016 at 6:39 AM, dudedoe01  wrote:

> What is the most feasible way to emulate the below MySQL function into
> postgreSQL. Since the isnull() function is no longer supported in 9.6
> version. I have tried every trick in the hat to get the desired results.
> Still 'RPG INV' doesn't show only the other two then options show up.
>
> (case
> when
> ((`s`.`Funding_Date` = '')
> and (isnull(`s`.`Actual_Close_Date`)
> or (`s`.`Actual_Close_Date` = '')))
> then
> 'RPG_INV'
> when
> ((isnull(`s`.`Funding_Date`)
> or (`s`.`Funding_Date` <> ''))
> and ((`s`.`Actual_Close_Date` = '')
> or isnull(`s`.`Actual_Close_Date`)))
> then
> 'Builder_Inventory'
> else 'Owner_Inventory'
> end) AS `Lot_Status`
>
>
>
>
Hi.  Over the years I have gotten a lot of patient assistance from this
mailing list, for which I am abundantly grateful.  In light of that, I
offer the following "translations," which are specifically in response to
your question about emulating the SQL above:

and (isnull(`s`.`Actual_Close_Date`)
--> and (`s`.`Actual_Close_Date` IS NULL)

((isnull(`s`.`Funding_Date`)
--> (`s`.`Funding_Date` IS NULL)

or isnull(`s`.`Actual_Close_Date`)))
-->or (`s`.`Actual_Close_Date` IS NULL)))

HTH, and Cheers.

Ken






> --
> View this message in context: http://postgresql.nabble.com/
> isnull-function-in-pgAdmin3-tp5923122p5924161.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
>



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Tom Lane]

Benedikt Grundmann  writes:
> proddb_testing=# SELECT
> conname,convalidated,conislocal,coninhcount,connoinherit
> proddb_testing-# FROM pg_constraint WHERE conrelid =
> 'js_activity_20110101'::regclass;
>conname   | convalidated | conislocal |
> coninhcount | connoinherit
> -+--++-+--
>  seqno_not_null  | f| t  |
>   1 | f

After some tracing through the code, I think it's the combination of all
three of coninhcount>0, conislocal, and !convalidated that is producing
the problem, and even then possibly only in binary-upgrade mode.  pg_dump
is jumping through some hoops to try to restore that state, and evidently
not getting it entirely right.

Is there a reason you've left all these constraints in NOT VALID state?
They're kinda useless that way.  Probably if you updated them to be valid
(see ALTER TABLE ... VALIDATE CONSTRAINT), the upgrade would go through
without difficulty.

I'll look into fixing this, but depending on how messy it turns out to be,
it might be something we choose to fix only in HEAD.

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] Understanding “max_wal_size” and “min_wal_size” parameters default values from postgresql.conf file

[Tom Lane]

otar shavadze  writes:
> name |  setting | unit--
> max_wal_size | 64   |
> min_wal_size | 5|

> I have 2 questions:

> 1) Why these values doesn't match default values, which are shown in docs?
> I never changed config settings at all.

They do match the defaults.

> 2) Why unit column is empty/NULL for these parameters? What means 64 and 5
> values in this case? MB? GB? or what?

The problem seems to be that somebody missed out adding GUC_UNIT_XSEGS
to the switch in GetConfigOptionByNum.  It should be showing you
something like "16MB" in the unit column, I think.

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] Understanding “max_wal_size” and “min_wal_size” parameters default values from postgresql.conf file

[otar shavadze]

According to documentation, for "min_wal_size" and "max_wal_size" parameters


default values are:

For max_wal_size: The default is 1 GB

For min_wal_size: The default is 80 MB

Then I look this parameters from my database config:

select name, setting, unit from pg_settings where name in
('min_wal_size', 'max_wal_size')

Gives result:

name |  setting | unit--
max_wal_size | 64   |
min_wal_size | 5|

I have 2 questions:

1) Why these values doesn't match default values, which are shown in docs?
I never changed config settings at all.

2) Why unit column is empty/NULL for these parameters? What means 64 and 5
values in this case? MB? GB? or what?

Why this is not like for example work_mem parameter, when everything is
clear:

name | setting  | unit--
work_mem | 4096 | kB

[GENERAL] Installing pgAdmin 4 in Oracle Enterprise Linux 7

[Edson Richter]

Dear community,


I'm trying to install pgAdmin4 in Oracle EL 7.

I've already installed PostgreSQL 9.6 final in same server (have EPEL enabled, 
as well pgdg 9.6 repos).

Running "yum install pgadmin4-web" I get the following result:


"[root@backup1 yum.repos.d]# LANG=C yum install pgadmin4-web

Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package pgadmin4-web.noarch 0:1.0-1.rhel7 will be installed
--> Processing Dependency: python-psycopg2-debug >= 2.6.2 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Processing Dependency: python-passlib >= 1.6.2 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Processing Dependency: python-html5lib >= 1.0b3 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Processing Dependency: python-crypto >= 2.6.1 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Processing Dependency: python-blinker >= 1.3 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Processing Dependency: python-click for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Running transaction check
---> Package pgadmin4-web.noarch 0:1.0-1.rhel7 will be installed
--> Processing Dependency: python-blinker >= 1.3 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
---> Package python-click.noarch 0:6.3-1.el7 will be installed
---> Package python-html5lib.noarch 1:0.999-5.el7 will be installed
---> Package python-passlib.noarch 0:1.6.2-2.el7 will be installed
---> Package python-psycopg2-debug.x86_64 0:2.6.2-2.rhel7 will be installed
--> Processing Dependency: libpython2.7_d.so.1.0()(64bit) for package: 
python-psycopg2-debug-2.6.2-2.rhel7.x86_64
---> Package python2-crypto.x86_64 0:2.6.1-9.el7 will be installed
--> Processing Dependency: libtomcrypt.so.0()(64bit) for package: 
python2-crypto-2.6.1-9.el7.x86_64
--> Running transaction check
---> Package libtomcrypt.x86_64 0:1.17-23.el7 will be installed
--> Processing Dependency: libtommath >= 0.42.0 for package: 
libtomcrypt-1.17-23.el7.x86_64
--> Processing Dependency: libtommath.so.0()(64bit) for package: 
libtomcrypt-1.17-23.el7.x86_64
---> Package pgadmin4-web.noarch 0:1.0-1.rhel7 will be installed
--> Processing Dependency: python-blinker >= 1.3 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
---> Package python-psycopg2-debug.x86_64 0:2.6.2-2.rhel7 will be installed
--> Processing Dependency: libpython2.7_d.so.1.0()(64bit) for package: 
python-psycopg2-debug-2.6.2-2.rhel7.x86_64
--> Running transaction check
---> Package libtommath.x86_64 0:0.42.0-4.el7 will be installed
---> Package pgadmin4-web.noarch 0:1.0-1.rhel7 will be installed
--> Processing Dependency: python-blinker >= 1.3 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
---> Package python-psycopg2-debug.x86_64 0:2.6.2-2.rhel7 will be installed
--> Processing Dependency: libpython2.7_d.so.1.0()(64bit) for package: 
python-psycopg2-debug-2.6.2-2.rhel7.x86_64
--> Finished Dependency Resolution
Error: Package: pgadmin4-web-1.0-1.rhel7.noarch (pgdg96)
   Requires: python-blinker >= 1.3
Error: Package: python-psycopg2-debug-2.6.2-2.rhel7.x86_64 (pgdg96)
   Requires: libpython2.7_d.so.1.0()(64bit)
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest"

(the "LANG=C" at beginning of the line is to force output in english, please 
ignore it)

Have anyone faced similar issue or know how to correct this error?

Thanks in advance,

Edson Richter

Re: [GENERAL] isnull() function in pgAdmin3

[dudedoe01]

What is the most feasible way to emulate the below MySQL function into
postgreSQL. Since the isnull() function is no longer supported in 9.6
version. I have tried every trick in the hat to get the desired results.
Still 'RPG INV' doesn't show only the other two then options show up. 

(case
when
((`s`.`Funding_Date` = '')
and (isnull(`s`.`Actual_Close_Date`)
or (`s`.`Actual_Close_Date` = '')))
then
'RPG_INV'
when
((isnull(`s`.`Funding_Date`)
or (`s`.`Funding_Date` <> ''))
and ((`s`.`Actual_Close_Date` = '')
or isnull(`s`.`Actual_Close_Date`)))
then
'Builder_Inventory'
else 'Owner_Inventory'
end) AS `Lot_Status`



--
View this message in context: 
http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5924161.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

[GENERAL]

[otar shavadze]

n6gSVg

Re: [GENERAL] ZSON, PostgreSQL extension for compressing JSONB

[Alan Gano]

I like this, seeing that the keys of JSON docs are replicated in every
record.

I makes my old-school DBA-Sense start to itch.



On Fri, Sep 30, 2016 at 8:58 AM, Aleksander Alekseev <
a.aleks...@postgrespro.ru> wrote:

> Hello.
>
> I've just uploaded ZSON extension on GitHub:
>
> https://github.com/afiskon/zson
>
> ZSON learns on your common JSONB documents and creates a dictionary
> with strings that are frequently used in all documents. After that you
> can use ZSON type to compress documents using this dictionary. When
> documents schema evolve and compression becomes inefficient you can
> re-learn on new documents. New documents will be compressed with a new
> dictionary, old documents will be decompressed using old dictionary.
>
> In some cases ZSON can save half of your disk space and give you about
> 10% more TPS. Everything depends on your data and workload though.
> Memory is saved as well. For more details see README.md.
>
> Please don't hesitate to ask any questions. Any feedback and pull
> requests are welcome too!
>
> --
> Best regards,
> Aleksander Alekseev
>

Re: [GENERAL] Installing pgAdmin 4 in Oracle Enterprise Linux 7

[Edson Richter]

On 03/10/2016 10:12, Devrim Gündüz wrote:
> Hi Edson,
>
> On Mon, 2016-10-03 at 00:18 +, Edson Richter wrote:
>> https://download.postgresql.org/pub/repos/yum/testing/9.6/redhat/rhel-7Server
>> -x86_64/repodata/repomd.xml:
>> [Errno 14] HTTPS Error 404 - Not Found
>> Trying other mirror.
> Fixed this error, sorry for that.
Thanks OK, this one is working - but I still get the following error:


[root@backup1 yum.repos.d]# LANG=C yum install pgadmin4-web
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package pgadmin4-web.noarch 0:1.0-1.rhel7 will be installed
--> Processing Dependency: python-psycopg2-debug >= 2.6.2 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Processing Dependency: python-passlib >= 1.6.2 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Processing Dependency: python-html5lib >= 1.0b3 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Processing Dependency: python-crypto >= 2.6.1 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Processing Dependency: python-blinker >= 1.3 for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Processing Dependency: python-click for package: 
pgadmin4-web-1.0-1.rhel7.noarch
--> Running transaction check
---> Package python-blinker.noarch 0:1.4-1.rhel7 will be installed
---> Package python-click.noarch 0:6.3-1.el7 will be installed
---> Package python-html5lib.noarch 1:0.999-5.el7 will be installed
---> Package python-passlib.noarch 0:1.6.2-2.el7 will be installed
---> Package python-psycopg2-debug.x86_64 0:2.6.2-2.rhel7 will be installed
--> Processing Dependency: libpython2.7_d.so.1.0()(64bit) for package: 
python-psycopg2-debug-2.6.2-2.rhel7.x86_64
---> Package python2-crypto.x86_64 0:2.6.1-9.el7 will be installed
--> Processing Dependency: libtomcrypt.so.0()(64bit) for package: 
python2-crypto-2.6.1-9.el7.x86_64
--> Running transaction check
---> Package libtomcrypt.x86_64 0:1.17-23.el7 will be installed
--> Processing Dependency: libtommath >= 0.42.0 for package: 
libtomcrypt-1.17-23.el7.x86_64
--> Processing Dependency: libtommath.so.0()(64bit) for package: 
libtomcrypt-1.17-23.el7.x86_64
---> Package python-psycopg2-debug.x86_64 0:2.6.2-2.rhel7 will be installed
--> Processing Dependency: libpython2.7_d.so.1.0()(64bit) for package: 
python-psycopg2-debug-2.6.2-2.rhel7.x86_64
--> Running transaction check
---> Package libtommath.x86_64 0:0.42.0-4.el7 will be installed
---> Package python-psycopg2-debug.x86_64 0:2.6.2-2.rhel7 will be installed
--> Processing Dependency: libpython2.7_d.so.1.0()(64bit) for package: 
python-psycopg2-debug-2.6.2-2.rhel7.x86_64
--> Finished Dependency Resolution
Error: Package: python-psycopg2-debug-2.6.2-2.rhel7.x86_64 (pgdg96)
Requires: libpython2.7_d.so.1.0()(64bit)
  You could try using --skip-broken to work around the problem
  You could try running: rpm -Va --nofiles --nodigest

Regards,

Edson

>
 Error: Package: python-psycopg2-debug-2.6.2-2.rhel7.x86_64
 (pgdg96)   Requires: libpython2.7_d.so.1.0()(64bit)
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest"
>>> This is also something that I need to test, but it is too late in here now,
>>> will look tomorrow.
> Hmm, AFAICS, RHEL and OEL do not have python-debug package. I'll take a look.
>
> Regards,
> -- 
> Devrim GÜNDÜZ
> EnterpriseDB: http://www.enterprisedb.com
> PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
> Twitter: @DevrimGunduz , @DevrimGunduzTR


-- 
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] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On 3 October 2016 at 15:54, Tom Lane  wrote:

> Benedikt Grundmann  writes:
> > And it looks like now I'm back to the error that stopped me last time:
> > pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282
> CHECK
> > CONSTRAINT seqno_not_null postgres_prod
> > pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
> > "seqno_not_null" for relation "js_activity_20110101" already exists
> >Command was: ALTER TABLE "js_activity_20110101"
> >ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
>
> Hm.  I'm guessing that table is a child table, and this has something to
> do with improper constraint inheritance.  Could we see psql \d+ output
> for both this table and its parent?  And for good measure, maybe the
> output of
>
> SELECT conname,convalidated,conislocal,coninhcount,connoinherit
> FROM pg_constraint WHERE conrelid = 'js_activity_20110101'::regclass;
>
> and likewise for the parent table.
>
> regards, tom lane
>

Sure this is not going to be very pretty but here you are:

proddb_testing=# \d+ js_activity
  Table
"public.js_activity"
  Column   |Type |
 Modifiers
| Storage  | Stats target | Description
---+-+-
+--+--+-
 id| text| not null default
nextval('pnl_fr_imported_trades_id_seq'::re
gclass) | extended |  |
 tid   | text| not null default
currval('pnl_fr_imported_trades_id_seq'::re
gclass) | extended |  |
 entry_time| timestamp without time zone | not null default
now()
| plain|  |
 pnl_date  | date| not null

| plain|  |
 activity_date | date| not null

| plain|  |
 activity_time | time without time zone  |

| plain|  |
 projected_settlement_date | date| not null

| plain|  |
 clearing_firm | text| not null

| extended |  |
 currency  | text| not null

| extended |  |
 account   | text| not null

| extended |  |
 security_code | text| not null

| extended |  |
 trade_type| text| not null

| extended |  |
 quantity  | numeric(28,8)   | not null

| main |  |
 gross_price   | numeric(28,8)   | not null default
0
| main |  |
 net_price | numeric(28,8)   | not null default
0
| main |  |
 net_cash  | numeric(28,8)   | not null default
0
| main |  |
 accrual   | numeric(28,8)   | not null default
0
| main |  |
 commission_amount | numeric(28,8)   | not null default
0
| main |  |
 commission_rate   | numeric(28,8)   | not null default
0
| main |  |
 narrative | text| not null default
''::text
| extended |  |
 counterparty  | text| not null

| extended |  |
 executing_exchange| text| not null default
''::text
| extended |  |
 route | text| not null default
''::text
| extended |  |
 source| text| not null default
''::text
| extended |  |
 tags  | hstore  | not null default
''::hstore
| extended |  |
 booking_type  | character(1)| not null default
'B'::bpchar
| extended |  |
 is_canceled   | text|

| extended |  |
 executing_broker  | text| not null default
''::text
| extended |  |
 version_tags  | hstore  | not null default
''::hstore
| extended |  |
 discretionary_agent   | text| not null default
''::text
| extended |  |
 shape_id  | text  

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Tom Lane]

Benedikt Grundmann  writes:
> And it looks like now I'm back to the error that stopped me last time:
> pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282 CHECK
> CONSTRAINT seqno_not_null postgres_prod
> pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
> "seqno_not_null" for relation "js_activity_20110101" already exists
>Command was: ALTER TABLE "js_activity_20110101"
>ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

Hm.  I'm guessing that table is a child table, and this has something to
do with improper constraint inheritance.  Could we see psql \d+ output
for both this table and its parent?  And for good measure, maybe the
output of

SELECT conname,convalidated,conislocal,coninhcount,connoinherit
FROM pg_constraint WHERE conrelid = 'js_activity_20110101'::regclass;

and likewise for the parent table.

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] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On 3 October 2016 at 15:30, Tom Lane  wrote:

> Benedikt Grundmann  writes:
> > On 3 October 2016 at 14:12, Tom Lane  wrote:
> >> You're going to need to manually drop that operator from the source
> >> database, as "=>" isn't a legal operator name anymore.  This appears
> >> to be left over from a pre-9.0 version of hstore.
>
> > Thanks for the quick reply.  How do I do that however?  Without dropping
> > the extension itself that is:
> >
> > proddb_testing=# drop operator => (text, text);
> > ERROR:  cannot drop operator =>(text,text) because extension hstore
> requires it
> > HINT:  You can drop extension hstore instead.
> >
> > Is it possible that I need to do some form of hstore extension upgrade
> > dance?
>
> Ah, I'd been guessing that the operator was "loose", but if you still
> have hstore 1.0 installed then yes that's the behavior I'd expect.
> You need to do "alter extension hstore update".  In a 9.2 database
> that should bring it to 1.1 which will get rid of the operator.
>
> regards, tom lane
>

And it looks like now I'm back to the error that stopped me last time:

[root@igm-dbc-001 ~]# cd /usr/local/home/as-proddb/upgrade-logs/
[root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282 CHECK
CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
"seqno_not_null" for relation "js_activity_20110101" already exists
Command was: ALTER TABLE "js_activity_20110101"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

I figured it might be because I'm on 9.2.*17* so I upgraded to 9.2.*18 *and
tried again but no luck:

[root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282 CHECK
CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
"seqno_not_null" for relation "js_activity_20110101" already exists
Command was: ALTER TABLE "js_activity_20110101"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;

What do you need from me to debug this?

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Tom Lane]

Benedikt Grundmann  writes:
> On 3 October 2016 at 14:12, Tom Lane  wrote:
>> You're going to need to manually drop that operator from the source
>> database, as "=>" isn't a legal operator name anymore.  This appears
>> to be left over from a pre-9.0 version of hstore.

> Thanks for the quick reply.  How do I do that however?  Without dropping
> the extension itself that is:
> 
> proddb_testing=# drop operator => (text, text);
> ERROR:  cannot drop operator =>(text,text) because extension hstore requires 
> it
> HINT:  You can drop extension hstore instead.
> 
> Is it possible that I need to do some form of hstore extension upgrade
> dance?

Ah, I'd been guessing that the operator was "loose", but if you still
have hstore 1.0 installed then yes that's the behavior I'd expect.
You need to do "alter extension hstore update".  In a 9.2 database
that should bring it to 1.1 which will get rid of the operator.

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] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On 3 October 2016 at 14:12, Tom Lane  wrote:

> Benedikt Grundmann  writes:
> > I just tried this again.  This time from 9.2.17 to 9.5.4 and pg_upgrade
> > chokes with this:
> >
> > [root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
> > pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error
> > at or near "=>"
> > LINE 1: CREATE OPERATOR => (
> > ^
> > Command was: CREATE OPERATOR => (
> > PROCEDURE = "tconvert",
> > LEFTARG = "text",
> > RIGHTARG = "text"
> > );
>
> You're going to need to manually drop that operator from the source
> database, as "=>" isn't a legal operator name anymore.  This appears
> to be left over from a pre-9.0 version of hstore.
>
> regards, tom lane
>

Thanks for the quick reply.  How do I do that however?  Without dropping
the extension itself that is:

postgres=# select * from pg_operator where oprname = '=>';
(0 rows)
postgres=# \c proddb_testing
psql (9.5.4, server 9.2.17)
You are now connected to database "proddb_testing" as user "as-proddb".
proddb_testing=# select * from pg_operator where oprname = '=>';
-[ RECORD 1 ]+-
oprname  | =>
oprnamespace | 2200
oprowner | 10
oprkind  | b
oprcanmerge  | f
oprcanhash   | f
oprleft  | 25
oprright | 25
oprresult| 144920
oprcom   | 0
oprnegate| 0
oprcode  | tconvert
oprrest  | -
oprjoin  | -

proddb_testing=# drop operator => (text, text);
ERROR:  cannot drop operator =>(text,text) because extension hstore
requires it
HINT:  You can drop extension hstore instead.

Is it possible that I need to do some form of hstore extension upgrade
dance?

Re: [GENERAL] [ANNOUNCE] pgAdmin 4 v1.0 Released!

[Adrian Klaver]

On 10/03/2016 02:27 AM, Tim Clarke wrote:

On 01/10/16 01:43, Adrian Klaver wrote:


Before you run the python setup.py part you will then have to install
a lot of  Flask dependencies:

Flask-Babel==0.11.1
Flask-Gravatar==0.4.2
Flask-Login==0.3.2
Flask-Mail==0.9.1
Flask-Principal==0.4.0
Flask-Security==1.7.5
Flask-SQLAlchemy==2.1
Flask-WTF==0.13

also:

django-htmlmin==0.9.1

Then run python setup.py.


Should those installs be into python 2 or python 3?


That would depend on what your system default is. I suspect 2.7. The way 
to check is from command line:


aklaver@panda:~> python -V
Python 2.7.12

This triggered a memory. When I was building the Qt app it failed as you 
posted with a cannot find flask module error. Then it popped up a dialog 
window asking for the Python path and the binary path. To get the 
program to work I had to enter the following for the Python path;


/usr/lib/python2.7/site-packages/;/usr/local/lib/python2.7/site-packages/

Your ~/site-packages directories may be in different locations. The 
/usr/local/* is because that is where Flask threw the itsdangerous module.


I did not enter anything for the binary path.





Personally I would go this route:

https://www.pgadmin.org/download/pip4.php



I'll research python virtual environments and try.


If you use the virtualenv you launch the Flask server in it and then use 
a browser to connect to it. The QT desktop app just automates that by 
launching the server in the background and then connecting via the 
embedded Qt browser.




Tim Clarke




--
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] Problems with pg_upgrade after change of unix user running db.

[Tom Lane]

Benedikt Grundmann  writes:
> I just tried this again.  This time from 9.2.17 to 9.5.4 and pg_upgrade
> chokes with this:
> 
> [root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
> pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error
> at or near "=>"
> LINE 1: CREATE OPERATOR => (
> ^
> Command was: CREATE OPERATOR => (
> PROCEDURE = "tconvert",
> LEFTARG = "text",
> RIGHTARG = "text"
> );

You're going to need to manually drop that operator from the source
database, as "=>" isn't a legal operator name anymore.  This appears
to be left over from a pre-9.0 version of hstore.

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] Installing pgAdmin 4 in Oracle Enterprise Linux 7

[Devrim Gündüz]

Hi Edson,

On Mon, 2016-10-03 at 00:18 +, Edson Richter wrote:
> https://download.postgresql.org/pub/repos/yum/testing/9.6/redhat/rhel-7Server
> -x86_64/repodata/repomd.xml: 
> [Errno 14] HTTPS Error 404 - Not Found
> Trying other mirror.

Fixed this error, sorry for that.

> >
> >> Error: Package: python-psycopg2-debug-2.6.2-2.rhel7.x86_64
> >> (pgdg96)   Requires: libpython2.7_d.so.1.0()(64bit)
> >>   You could try using --skip-broken to work around the problem
> >>   You could try running: rpm -Va --nofiles --nodigest"
> > This is also something that I need to test, but it is too late in here now,
> > will look tomorrow.

Hmm, AFAICS, RHEL and OEL do not have python-debug package. I'll take a look.

Regards,
-- 
Devrim GÜNDÜZ
EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On 30 November 2015 at 17:01, Bruce Momjian  wrote:

> On Mon, Nov 30, 2015 at 04:51:15PM +, Benedikt Grundmann wrote:
> > Are you able to compile from 9.4 git head and test that?  It seems
> > dumping inheriting constraints from parents has not worked properly
> for
> > some time.
> >
> >
> > Do I need to get the latest/head 9.2 or the latest/head 9.4 or both?
> For what
> > it is worth I just tried after upgrading to the latest released 9.2 (and
> same
> > 9.45) and that didn't work :-(
>
> You actually need non-released 9.4.X code that is in pg_dump, and we use
> 9.4 pg_dump to dump the 9.2 database.
>
> > I should certainly be able to compile from source.  But the upgrade to
> 9.4 is
> > by far not high on my priority stack (other than maybe some speed wins
> there is
> > nothing in 9.4 that we are eager for, there are some niceties but I can
> happily
> > live without all of them for years) and has already consumed way more
> time than
> > I had scheduled for it.  So I'll return to focus on other work for at
> least
> > this week and maybe more depending on how that work goes.
> >
> > Thanks to everyone I'll certainly update this thread if / when I have
> more time
> > to devote to this.
>
> The simplest solution is to wait for 9.4.6 to be released and test that.
>
>
I just tried this again.  This time from 9.2.17 to 9.5.4 and pg_upgrade
chokes with this:

[root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error
at or near "=>"
LINE 1: CREATE OPERATOR => (
^
Command was: CREATE OPERATOR => (
PROCEDURE = "tconvert",
LEFTARG = "text",
RIGHTARG = "text"
);

-- For binary upgrade, hand...


Any pointers are appreciated.

Thanks,

Bene




> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I. As I am, so you will be. +
> + Roman grave inscription +
>

Re: [GENERAL] [ANNOUNCE] pgAdmin 4 v1.0 Released!

[Tim Clarke]

On 01/10/16 01:43, Adrian Klaver wrote:
>
> Before you run the python setup.py part you will then have to install
> a lot of  Flask dependencies:
>
> Flask-Babel==0.11.1
> Flask-Gravatar==0.4.2
> Flask-Login==0.3.2
> Flask-Mail==0.9.1
> Flask-Principal==0.4.0
> Flask-Security==1.7.5
> Flask-SQLAlchemy==2.1
> Flask-WTF==0.13
>
> also:
>
> django-htmlmin==0.9.1
>
> Then run python setup.py.

Should those installs be into python 2 or python 3?

>
> Personally I would go this route:
>
> https://www.pgadmin.org/download/pip4.php
>

I'll research python virtual environments and try.

Tim Clarke



smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] [ANNOUNCE] pgAdmin 4 v1.0 Released!

[Tim Clarke]

On 30/09/16 21:24, Adrian Klaver wrote:
>
> What happens if you move the pgAdmin4 runtime outside the build
> environment and run it?

I receive the message "Failed to locate pgAdmin4.py, terminating server
thread."

>
> Can you import Flask in a Python interpreter?

Yes, that works fine.

>
> Do you have multiple versions of Python and if so are you sure the
> apt-get is installing Flask into the same version that pgAdmin4 is using?
>

In examining that I find that I have 2.7 and 3.5.2 installed. "sudo apt
list --installed" shows both python-flask and python3-flask, probably
both installed as I flailed around trying to install pgAdmin4.

Tim Clarke



smime.p7s
Description: S/MIME Cryptographic Signature