Upgrading 9.2 to 9.6 questions

[Lucas Possamai]

Hi.

We have a setup of 3 Postgres 9.2 nodes (1x master, 2x slaves) running on
EC2 instances in AWS. We want to upgrade to 9.6 so we can move to Amazon
RDS (it requires at least a 9.3.5 version

).
As far as I know, we have 3 options here:


   1. From 9.2 on EC2 to RDS
  1. pg_dump on 9.2
  2. Restore it in RDS (might not work; haven't tested it)
   2. Get Slony replication working
  1. Setup slony in our 9.2 nodes.
  2. Get a fresh Postgres installation running Postgres 9.6
  3. Copy the database over (pg_basebackup)
  4. Start the replication (master 9.2 --> slave 9.2 --> slave 9.6)
  5. failover to 9.6
  6. re-create slaves from 9.6
   3. From 9.2 to 9.6 on EC2; then to RDS:
   1. upgrade the master 9.2 to 9.6
  2. re-create all slaves with 9.6
  3. after a while (months/weeks), use DMS or pg_dump/pg_restore and go
  for RDS

Because of downtime, *Step 2* seems more with what we wanna go forward.

Questions are:

   - At the moment I am using native Postgres streaming replication with
   two slaves. Do I need to install Slony on my master, and get rid of the
   native tool? and then re-create the slaves with slony?
   - Slony or Bucardo are trigger-based replication solutions. That means
   DDL changes won't be propagated. This is not good as new tables can be
   added at any time. Isn't there a workaround for this?

Thanks!

Java's org.postgresql.util.PSQLState is missing common PostgreSQL Error Codes

[David Kremer]

In my Java API server, I am using SERIALIZABLE transaction isolation mode, so 
I'm specially handling the error code of "40001 serialization_failure", which 
can occur often. I'm getting the error code String using SQLException's 
getSQLState() function.

Therefore I was surprised to see that the Java enum 
org.postgresql.util.PSQLState does not include this error code.

PSQLState code: 
https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/util/PSQLState.java
PSQLState documentation: 
https://jdbc.postgresql.org/development/privateapi/org/postgresql/util/PSQLState.html

The 40001 serialization_failure error is listed here: 
https://www.postgresql.org/docs/11/errcodes-appendix.html

It seems like this should be added to the pgjdbc Java enum. Is there a reason 
it's not in there?

Re: ALTER DEFAULT PRIVILEGES FOR ROLE

[Adrian Klaver]

On 1/30/19 10:57 AM, Hilbert, Karin wrote:
After a database was updated by the application, a schema dump showed 
the following default privilege statements:


--
-- Name: DEFAULT PRIVILEGES FOR SEQUENCES; Type: DEFAULT ACL; Schema: 
public; Owner: gitlab_dbo

--

ALTER DEFAULT PRIVILEGES FOR ROLE  IN SCHEMA public REVOKE ALL 
ON SEQUENCES  FROM ;
ALTER DEFAULT PRIVILEGES FOR ROLE  IN SCHEMA public GRANT 
SELECT,USAGE ON SEQUENCES  TO ;



--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: 
public; Owner: 

--

ALTER DEFAULT PRIVILEGES FOR ROLE  IN SCHEMA public REVOKE ALL 
ON TABLES  FROM ;
ALTER DEFAULT PRIVILEGES FOR ROLE  IN SCHEMA public GRANT 
SELECT,INSERT,DELETE,UPDATE ON TABLES  TO ;


Why would you want to revoke all privileges from the dbowner?


You would have to ask the application developer.

It actually had granted the privileges to PUBLIC, but I revoked those 
privileges & changed it to the app account.


This seems to be a continuation of your previous post. It would seem you 
and the application developer need to have a head to head and agree on 
what the privilege/permissions policy for this application/database 
needs to be.





What is the difference between these statements?:
ALTER DEFAULT PRIVILEGES FOR ROLE  IN SCHEMA public GRANT ...  
TO ;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ... TO ;


The first is keyed to a ROLE, the second to a SCHEMA.




Karin Hilbert
Database Specialist
Administrative Information Services
Pennsylvania State University
25 Shields Bldg., University Park, PA  16802
Work - 814-863-3633
Email - i...@psu.edu
IM - i...@chat.psu.edu




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

ALTER DEFAULT PRIVILEGES FOR ROLE

[Hilbert, Karin]

After a database was updated by the application, a schema dump showed the 
following default privilege statements:

--
-- Name: DEFAULT PRIVILEGES FOR SEQUENCES; Type: DEFAULT ACL; Schema: public; 
Owner: gitlab_dbo
--

ALTER DEFAULT PRIVILEGES FOR ROLE  IN SCHEMA public REVOKE ALL ON 
SEQUENCES  FROM ;
ALTER DEFAULT PRIVILEGES FOR ROLE  IN SCHEMA public GRANT SELECT,USAGE 
ON SEQUENCES  TO ;


--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: public; 
Owner: 
--

ALTER DEFAULT PRIVILEGES FOR ROLE  IN SCHEMA public REVOKE ALL ON 
TABLES  FROM ;
ALTER DEFAULT PRIVILEGES FOR ROLE  IN SCHEMA public GRANT 
SELECT,INSERT,DELETE,UPDATE ON TABLES  TO ;

Why would you want to revoke all privileges from the dbowner?
It actually had granted the privileges to PUBLIC, but I revoked those 
privileges & changed it to the app account.


What is the difference between these statements?:
ALTER DEFAULT PRIVILEGES FOR ROLE  IN SCHEMA public GRANT ...  TO 
;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ... TO ;




Karin Hilbert
Database Specialist
Administrative Information Services
Pennsylvania State University
25 Shields Bldg., University Park, PA  16802
Work - 814-863-3633
Email - i...@psu.edu
IM - i...@chat.psu.edu

Re: Old tsearch functions

[Tom Lane]

Howard News  writes:
> On 30/01/2019 17:20, Adrian Klaver wrote:
>> Is to late to experiment with dropping the extension on a 9.5 database 
>> and then dumping?

> Not a problem - The problem only shows in development so far. I have all 
> the originals in 9.5 and backups to try this on.

Note that if you had those functions laying around ever since 8.3,
they're probably just "loose" and not wrapped into an extension at all.

You could fix that in a 9.5 database by running

create extension tsearch2 from unpackaged;

which should be enough to collect the relevant objects into an
extension.  At that point you could try doing "drop extension tsearch2".
Likely it'll fail due to dependencies on the extension objects, but
at least the error message will give you an idea of what you need to
fix before you can drop it.  In any case, this certainly beats trying
to manually identify and drop the obsolete types and functions.

You will need to do this in 9.5, or at the latest 9.6, because we
dropped support for that extension in v10.

regards, tom lane

Re: Old tsearch functions

[Howard News]

On 30/01/2019 17:20, Adrian Klaver wrote:


Is to late to experiment with dropping the extension on a 9.5 database 
and then dumping?



Not a problem - The problem only shows in development so far. I have all 
the originals in 9.5 and backups to try this on.

Re: Old tsearch functions

[Adrian Klaver]

On 1/30/19 9:07 AM, Howard News wrote:

Hi,

upgrading from 9.5 to 11.1, I have a few tsearch problems that appear to 
relate to a much older migration (8.3)


Originally, for those that remember that far back, tsearch was a contrib 
module. However it appears that I still have some of the old tsearch 
functions in my database, and these were copied into the pg_dump that I 
restored into v11 and have caused some confusion and problems.


For example, select to_tsquery('english', 'hello') would not work. The 
reason is the additional functions, domains etc that were added to the 
public schema, probably from the contrib module, which was removed in v10,


Now I can delete all these extra functions and domains and change the 
types of all my tsvector columns from "public.tsvector" to the inbuilt 
type "tsvector", but is there an easier way or a script that exists to 
do this automatically? (I have a lot of databases with this issue)


Is to late to experiment with dropping the extension on a 9.5 database 
and then dumping?




Thanks

Howard.





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

Old tsearch functions

[Howard News]

Hi,

upgrading from 9.5 to 11.1, I have a few tsearch problems that appear to 
relate to a much older migration (8.3)


Originally, for those that remember that far back, tsearch was a contrib 
module. However it appears that I still have some of the old tsearch 
functions in my database, and these were copied into the pg_dump that I 
restored into v11 and have caused some confusion and problems.


For example, select to_tsquery('english', 'hello') would not work. The 
reason is the additional functions, domains etc that were added to the 
public schema, probably from the contrib module, which was removed in v10,


Now I can delete all these extra functions and domains and change the 
types of all my tsvector columns from "public.tsvector" to the inbuilt 
type "tsvector", but is there an easier way or a script that exists to 
do this automatically? (I have a lot of databases with this issue)


Thanks

Howard.

Re: Major upgrade from 9.6.10 to 10.6 and pg_stat_statement update

[Adrian Klaver]

On 1/30/19 7:14 AM, Thomas Poty wrote:

Hi,

I am about to upgrade from 9.6.10 to 10.6 .
I read, in the release notes of the 9.6.11 and of the 10.6, an "alter 
extension pg_stat_statements update" must be done.


So, after my upgrade, must the update of pg_statements be done ?


Per the notes:

https://www.postgresql.org/docs/10/release-10-6.html#id-1.11.6.5.4

"In contrib/pg_stat_statements, disallow the pg_read_all_stats role from 
executing pg_stat_statements_reset() (Haribabu Kommi)


pg_read_all_stats is only meant to grant permission to read statistics, 
not to change them, so this grant was incorrect.


To cause this change to take effect, run ALTER EXTENSION 
pg_stat_statements UPDATE in each database where pg_stat_statements has 
been installed.

"

So if you have the extension installed then yes. You can check in each 
database, using the psql command:


\dx




Thanks

Thomas



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

Major upgrade from 9.6.10 to 10.6 and pg_stat_statement update

[Thomas Poty]

Hi,

I am about to upgrade from 9.6.10 to 10.6 .
I read, in the release notes of the 9.6.11 and of the 10.6, an "alter
extension pg_stat_statements update" must be done.

So, after my upgrade, must the update of pg_statements be done ?

Thanks

Thomas

Re: pgexpress 4.60 vita voom

[Ray O'Donnell]

On 30/01/2019 14:19, Ray O'Donnell wrote:

On 30/01/2019 11:22, ceuro wrote:

Hello everybody, this is my first post.
I need to update to vita voom pgexpress 4.60 in order to upgrade to
postgresql 9.2:
I've tried to contact vita voom to collect (I've also already payed) the
last release of the driver but nobody answer me:
Is there anybody that could help me in finding the driver?
What you suggest to use instead of pgexpress to connect new release of
postgresql 9.2?
(of course for me it should be easier to use pgexpress as I already 
use an

old release to connect postgresql 8.2)


Hi there,

It's a proprietary driver, so you'll have to pursue Vita Voom, if they 
still exist.


Actually, I just had a quick look at the Vita Voom website, and the last 
news item on it is dated October 2012 so that's not a good sign.


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: pgexpress 4.60 vita voom

[Ray O'Donnell]

On 30/01/2019 11:22, ceuro wrote:

Hello everybody, this is my first post.
I need to update to vita voom pgexpress 4.60 in order to upgrade to
postgresql 9.2:
I've tried to contact vita voom to collect (I've also already payed) the
last release of the driver but nobody answer me:
Is there anybody that could help me in finding the driver?
What you suggest to use instead of pgexpress to connect new release of
postgresql 9.2?
(of course for me it should be easier to use pgexpress as I already use an
old release to connect postgresql 8.2)


Hi there,

It's a proprietary driver, so you'll have to pursue Vita Voom, if they 
still exist.


I used to use it years ago for Delphi stuff, but I got tired of having 
to buy it all over again whenever I upgraded PostgreSQL, so I moved 
elsewhere. I've no problem paying for good software, but I didn't like 
having to do it over and over again.


A separate issue is that PostgreSQL 9.2 is very old indeed, and is no 
longer supported.


Ray.


--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: pgexpress 4.60 vita voom

[Ron]

On 1/30/19 5:22 AM, ceuro wrote:

Hello everybody, this is my first post.
I need to update to vita voom pgexpress 4.60 in order to upgrade to
postgresql 9.2:
I've tried to contact vita voom to collect (I've also already payed) the
last release of the driver but nobody answer me:
Is there anybody that could help me in finding the driver?
What you suggest to use instead of pgexpress to connect new release of
postgresql 9.2?
(of course for me it should be easier to use pgexpress as I already use an
old release to connect postgresql 8.2)


v9.2 has been EOL for more than a year. 
https://www.postgresql.org/support/versioning/


Push for upgrading to something more modern.


--
Angular momentum makes the world go 'round.

pgexpress 4.60 vita voom

[ceuro]

Hello everybody, this is my first post.
I need to update to vita voom pgexpress 4.60 in order to upgrade to
postgresql 9.2:
I've tried to contact vita voom to collect (I've also already payed) the
last release of the driver but nobody answer me:
Is there anybody that could help me in finding the driver?
What you suggest to use instead of pgexpress to connect new release of
postgresql 9.2?
(of course for me it should be easier to use pgexpress as I already use an
old release to connect postgresql 8.2)

Thank you in advance
Kind Regards
Mauro Casasola



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: pg_rewind success even though getting error 'record with incorrect prev-link'

[Abdullah Al Maruf]

> The only *error* I see is when you apparently manually kill the process.

You mean walreceiver process?? 'FATAL: terminating walreceiver process due
to administrator command' ?
Actually, I didn't kill the receiver. It is done by postgres itself, as far
as I understand.

I restart this node using a replica script. You can see the script here.
https://github.com/kubedb/postgres/blob/pg-db/hack/docker/postgres/9.6.7/scripts/replica/run.sh
It then gives the above error, then stops streaming from primary (or, you
can see the wal receiver is terminated by itself).

If I restart this node again, It throws following error:

LOG:  skipping missing configuration file "/etc/config/user.conf"
LOG:  skipping missing configuration file "/etc/config/user.conf"
LOG:  0: database system was shut down in recovery at 2019-01-23
05:12:17 UTC
LOG:  0: entering standby mode
LOG:  0: invalid resource manager ID in primary checkpoint record
PANIC:  XX000: could not locate a valid checkpoint record
LOG:  0: startup process (PID 33) was terminated by signal 6
LOG:  0: aborting startup due to startup process failure
LOG:  0: database system is shut down

On Wed, Jan 30, 2019 at 12:02 PM Ron  wrote:

> The only *error* I see is when you apparently manually kill the process.
> The LOG messages aren't actually errors.
>
> --
> Angular momentum makes the world go 'round.
>