Question on Open PostgreSQL Monitoring
Hello, I am not sure if this is the right email group to ask about Open PostgreSQL Monitoring (https://opm.readthedocs.io/opm-core/index.html). It appears there is no update to this tool since at least 2019. Since members from this email group has lots of expertise on PostgreSQL, I thought I ask if anyone knows of a better tool to replace OPM for PG? Thanks for your feedback in advance. Dan IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
Question on tablefunc extension
Hello, We downloaded postgresql software from this site, https://www.postgresql.org/ftp/source/v12.1/, as a tar ball file (postgresql-12.1.tar.bz2). So we didn't do install the 'postgresql-contrib' option. Our developers asked that we add on the "tablefunc" extension to the existing postgresql instance. It appears there is no source code (*.tar.gz file) for "tablefunc". Is that correct? What is the easiest way to get the "tablefunc" installed as an extension to the existing postgresql instance? Thanks for your help! Dan IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
pg_upgrade question
Hi PostgreSQL Admin, I am trying to find out if there is any step by step instruction to reconcile old data dir and upgraded data dir after using "-link" option to do an upgrade. I ran this to do an upgrade from 11.5 to 12.1: pg_upgrade -d /hostname/pg/dev115/data -D /hostname/pg/dev121upg/data --link -b /pgdbadevbal800/pg/PostgreSQL-11.5/bin -B /pgdbadevbal800/pg/PostgreSQL-12.1/bin -p 1432 -P 2432 -v postgresdbad:dev115:pgdbadevbal800:> pwd /hostname/pg postgresdbad:dev115:pgdbadevbal800:> du -sh dev121upg 2.3Gdev121upg postgresdbad:dev115:pgdbadevbal800:> du -sh dev115 22G dev115 My goal is to be able to do an in place upgrade from 11.5 to 12.1 using the same data dir "/hostname/pg/dev115/data". Without the "-link" option I need to double up the space usage for the instance. What is the easiest way to accomplish this task? Thanks so much for your help. Dan IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Upgrade check failed from 11.5 to 12.1
Thanks Adrian. This is what I am seeing. postgresdbad:dpoc:pgdbadevbal800:> pg_controldata -D /hostname/pg/dev12upg/data | grep -i "Bytes per WAL segment" Bytes per WAL segment:131072 postgresdbad:dpoc:pgdbadevbal800:> pg_controldata -D /hostname/pg/dpoc/data | grep -i "Bytes per WAL segment" Bytes per WAL segment:16777216 -Original Message- From: Adrian Klaver Sent: Saturday, December 19, 2020 10:28 AM To: Lu, Dan ; Tom Lane Cc: pgsql-gene...@postgresql.org Subject: Re: Upgrade check failed from 11.5 to 12.1 On 12/18/20 2:56 PM, Lu, Dan wrote: > Thanks for catching that. Typo on my end. > > Now I am getting this. > > Performing Consistency Checks on Old Live Server > > Checking cluster versions ok > > old and new pg_controldata WAL segment sizes are invalid or do not > match Failure, exiting > > I found the setting in the current version of the instance via "show all" as > "wal_segment_size | 16MB". I guess my new instance > should be "16MB" as well when running this "initdb --wal-segsize=16 -D > /hostname/pg/NewInstance/data"? > Well the default is 16MB so you should not have to set it. What does: pg_controldata -D <11.5 data dir> pg_controldata -D <12.1 data dir> show for the setting Bytes per WAL segment: ? -- Adrian Klaver adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com> IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Upgrade check failed from 11.5 to 12.1
Thanks for catching that. Typo on my end. Now I am getting this. Performing Consistency Checks on Old Live Server Checking cluster versions ok old and new pg_controldata WAL segment sizes are invalid or do not match Failure, exiting I found the setting in the current version of the instance via "show all" as "wal_segment_size | 16MB". I guess my new instance should be "16MB" as well when running this "initdb --wal-segsize=16 -D /hostname/pg/NewInstance/data"? -Original Message- From: Tom Lane Sent: Friday, December 18, 2020 4:55 PM To: Lu, Dan Cc: pgsql-gene...@postgresql.org Subject: Re: Upgrade check failed from 11.5 to 12.1 "Lu, Dan" writes: > I am trying to upgrade an instance of PostgreSQL in unix from 11.5 to 12.1. > UNIX:> pg_upgrade -d /hostname/pg/dpoc/data -D /hostname/pg/dpoc/data > -b /hostname/pg/PostgreSQL-11.5/bin -B > /hostname/pg/PostgreSQL-12.1/bin -p 5432 -P 9432 -c -v No, you can't use the same directory to hold old and new versions at the same time. After you're done with the upgrade, you could move the new data directory to be where the old one had been. regards, tom lane IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
Upgrade check failed from 11.5 to 12.1
Hello PostgreSQL experts, I am trying to upgrade an instance of PostgreSQL in unix from 11.5 to 12.1. I got the following error on upgrade verification step. Is this not allowed? UNIX:> pg_upgrade -d /hostname/pg/dpoc/data -D /hostname/pg/dpoc/data -b /hostname/pg/PostgreSQL-11.5/bin -B /hostname/pg/PostgreSQL-12.1/bin -p 5432 -P 9432 -c -v Performing Consistency Checks - Checking cluster versions New cluster data and binary directories are from different major versions. Failure, exiting IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
Question on removing primary replication node using repmgr?
Hello, I used repmgr extension for Postgresql to setup replication. I want to remove the replication because it appears WAL files are generated even after unregistering the standby instance and putting primary instance in no archivelog mode. Can you help? host:> repmgr -f /home/postgresdbad/dbalocal/admin/repmgr.conf primary unregister ERROR: node "host" (ID: 1) is the current primary node, unable to unregister host:> repmgr -f /home/postgresdbad/dbalocal/admin/repmgr.conf primary unregister --node-id 1 ERROR: node "host" (ID: 1) is the current primary node, unable to unregister ID | Name | Role| Status| Upstream | Location | Priority | Timeline | Connection string ++-+---+--+--+--+--+- 1 | host | primary | * running | | default | 100 | 1| host=host user=repmgr dbname=repmgr connect_timeout=2 IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
Question on banner display after PG initial connection
Hello PostgreSQL Admin, I am fairly new to PostgreSQL. I am a curious question regarding the banner message displayed after connecting to version 12.1 of PostgreSQL. Is there a way to get rid of this line "SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)"? Thanks. UNIX:> psql 'host=hostname user=repmgr dbname=repmgr connect_timeout=2' psql (12.1) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. repmgr=# \q IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Question on upgrading postgresql from 10.7 to 11.5
Thanks! I was misled by "pg_controldata" keyword and totally ignored the WAL segment size variable. Much appreciated for your help. -Original Message- From: Julien Rouhaud [mailto:rjuju...@gmail.com] Sent: Wednesday, December 25, 2019 10:36 AM To: Lu, Dan Cc: pgsql-gene...@postgresql.org Subject: Re: Question on upgrading postgresql from 10.7 to 11.5 On Wed, Dec 25, 2019 at 3:42 PM Lu, Dan wrote: > > Hello, > > I am trying to upgrade my PG instance from 10.7 to 11.5. > > I got an error in doing so. Any idea what I am missing? > > Example: pg_upgrade -d /hostname/pg/dpoc/data -D > /hostname/pg/dpoc115/data -b /hostname/pg/PostgreSQL-10.7/bin -B > /hostname/pg/PostgreSQL-11.5/bin -p 5432 -P 9432 -c -v > > Error message: > > old and new pg_controldata WAL segment sizes are invalid or do not > match The message is pretty much self explanatory. It looks like you used "initdb --wal-segsize" for the 11.5 cluster using a value different from the one that were used in your 10.7 cluster (probably 16MB). You can check the value for each using "SHOW wal_segment_size". You have to recreate a new pg11 data directory using the same value as the 10.7 one. While at it, you should update the pg11 binary to the latest minor version, 11.6. IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
Question on upgrading postgresql from 10.7 to 11.5
Hello, I am trying to upgrade my PG instance from 10.7 to 11.5. I got an error in doing so. Any idea what I am missing? Example: pg_upgrade -d /hostname/pg/dpoc/data -D /hostname/pg/dpoc115/data -b /hostname/pg/PostgreSQL-10.7/bin -B /hostname/pg/PostgreSQL-11.5/bin -p 5432 -P 9432 -c -v Error message: old and new pg_controldata WAL segment sizes are invalid or do not match Failure, exiting IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Postgres 10.7 Systemd Startup Issue
Thank you so much for your help! -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, October 14, 2019 2:19 PM To: Lu, Dan Cc: Christopher Browne ; Francisco Olarte ; pgsql-gene...@postgresql.org Subject: Re: Postgres 10.7 Systemd Startup Issue "Lu, Dan" writes: > Is there a catalog view that I can query to see what options were used > to configure the PG instance? No, but pg_config should tell you. regards, tom lane IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Postgres 10.7 Systemd Startup Issue
Greeting, I have a follow-up question regarding PostgreSQL configure option. Example: ./configure --prefix=/hostname/pg/PostgreSQL-11.5 --with-systemd --with-openssl Above example I configure PG with 2 options: 1. with-systemd 2. with-openssl Is there a catalog view that I can query to see what options were used to configure the PG instance? We will be taking over administration of existing PG instance, but we would need to know what configuration option was used during build. Your feedback and help is much appreciated. Dan -Original Message- From: Lu, Dan Sent: Friday, June 07, 2019 3:22 PM To: 'Tom Lane' Cc: 'Christopher Browne' ; 'Francisco Olarte' ; Kelly, Kevin ; 'pgsql-gene...@postgresql.org' Subject: RE: Postgres 10.7 Systemd Startup Issue Hello All, I ended up removing all configuration. Re-unzip the binary tar files we downloaded. Re-configure with the option "--with-systemd" and now it is working. We really appreciate all your help! Dan -Original Message- From: Lu, Dan Sent: Friday, June 07, 2019 10:11 AM To: 'Tom Lane' Cc: Christopher Browne ; Francisco Olarte ; Kelly, Kevin ; pgsql-gene...@postgresql.org Subject: RE: Postgres 10.7 Systemd Startup Issue I apologize, it was a copy/paste error. I did include the option "--with-systemd". This is our first install of postgresql. I am in the process of getting standard in place for future deployment. We don't have any large footprint of postgresql here. We run mostly Oracle, SQL Server and MySQL in production so far. Dan -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, June 07, 2019 10:04 AM To: Lu, Dan Cc: Christopher Browne ; Francisco Olarte ; Kelly, Kevin ; pgsql-gene...@postgresql.org Subject: Re: Postgres 10.7 Systemd Startup Issue "Lu, Dan" writes: > Even with the added option “—with-systemd”, it is not working still. Hmmm > To outline what I did. > 2) Configure PostgreSQL to new location mkdir > /hostname/pg/PostgreSQL-10.7_2 ./configure > --prefix=/hostname/pg/PostgreSQL-10.7_2 Is it just a copy-and-paste mistake that you don't actually have a --with-systemd option here? Also, it seems fairly unusual for anyone to build a production installation with absolutely no configuration options --- it'd work, but you'd get a really plain-vanilla server. You might consider reviewing https://www.postgresql.org/docs/10/install-procedure.html to see what other things might make sense to turn on. regards, tom lane IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: postgresql: Help with hstore hextension
Thanks for the information. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, September 05, 2019 12:06 PM To: Lu, Dan Cc: pgsql-gene...@postgresql.org; Kelly, Kevin Subject: Re: postgresql: Help with hstore hextension "Lu, Dan" writes: > We are now trying to create "hstore" extension and ran into the error below. > opm=# CREATE EXTENSION hstore; > ERROR: could not open extension control file > "/pgdbadevbal801/pg/PostgreSQL-10.7/share/postgresql/extension/hstore. > control": No such file or directory You need to build/install the contrib/hstore module, or possibly you'd just want to install all of contrib. "make all" and "make install" at the top level don't touch the contrib subtree. If you already wiped your build tree, you'll have to recreate it --- be careful to give the same configure arguments as you used before. regards, tom lane IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
postgresql: Help with hstore hextension
Hello, We recently downloaded the "postgresql-10.7.tar.bz2" file from https://ftp.postgresql.org/pub/source/v10.7. We configure our installation as followed: ./configure --prefix=/hostname/pg/PostgreSQL-10.7 --with-systemd We created new database and things are working fine. We are now trying to create "hstore" extension and ran into the error below. opm=# CREATE EXTENSION hstore; ERROR: could not open extension control file "/pgdbadevbal801/pg/PostgreSQL-10.7/share/postgresql/extension/hstore.control": No such file or directory I am very new to PostgreSQL work and greatly appreciate your expertise in guiding me through this error. Is there a way we can add that extension into this installation we did like the "with-systemd" option above? Dan IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Postgres 10.7 Systemd Startup Issue
Hello All, I ended up removing all configuration. Re-unzip the binary tar files we downloaded. Re-configure with the option "--with-systemd" and now it is working. We really appreciate all your help! Dan -Original Message----- From: Lu, Dan Sent: Friday, June 07, 2019 10:11 AM To: 'Tom Lane' Cc: Christopher Browne ; Francisco Olarte ; Kelly, Kevin ; pgsql-gene...@postgresql.org Subject: RE: Postgres 10.7 Systemd Startup Issue I apologize, it was a copy/paste error. I did include the option "--with-systemd". This is our first install of postgresql. I am in the process of getting standard in place for future deployment. We don't have any large footprint of postgresql here. We run mostly Oracle, SQL Server and MySQL in production so far. Dan -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, June 07, 2019 10:04 AM To: Lu, Dan Cc: Christopher Browne ; Francisco Olarte ; Kelly, Kevin ; pgsql-gene...@postgresql.org Subject: Re: Postgres 10.7 Systemd Startup Issue "Lu, Dan" writes: > Even with the added option “—with-systemd”, it is not working still. Hmmm > To outline what I did. > 2) Configure PostgreSQL to new location mkdir > /hostname/pg/PostgreSQL-10.7_2 ./configure > --prefix=/hostname/pg/PostgreSQL-10.7_2 Is it just a copy-and-paste mistake that you don't actually have a --with-systemd option here? Also, it seems fairly unusual for anyone to build a production installation with absolutely no configuration options --- it'd work, but you'd get a really plain-vanilla server. You might consider reviewing https://www.postgresql.org/docs/10/install-procedure.html to see what other things might make sense to turn on. regards, tom lane IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Postgres 10.7 Systemd Startup Issue
I apologize, it was a copy/paste error. I did include the option "--with-systemd". This is our first install of postgresql. I am in the process of getting standard in place for future deployment. We don't have any large footprint of postgresql here. We run mostly Oracle, SQL Server and MySQL in production so far. Dan -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, June 07, 2019 10:04 AM To: Lu, Dan Cc: Christopher Browne ; Francisco Olarte ; Kelly, Kevin ; pgsql-gene...@postgresql.org Subject: Re: Postgres 10.7 Systemd Startup Issue "Lu, Dan" writes: > Even with the added option “—with-systemd”, it is not working still. Hmmm > To outline what I did. > 2) Configure PostgreSQL to new location mkdir > /hostname/pg/PostgreSQL-10.7_2 ./configure > --prefix=/hostname/pg/PostgreSQL-10.7_2 Is it just a copy-and-paste mistake that you don't actually have a --with-systemd option here? Also, it seems fairly unusual for anyone to build a production installation with absolutely no configuration options --- it'd work, but you'd get a really plain-vanilla server. You might consider reviewing https://www.postgresql.org/docs/10/install-procedure.html to see what other things might make sense to turn on. regards, tom lane IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Postgres 10.7 Systemd Startup Issue
Even with the added option “—with-systemd”, it is not working still. Status remains “ Active: activating (start) since Fri 2019-06-07 09:40:06 EDT; 13s ago”. To outline what I did. 1) Stop PostgreSQL instance running with “/hostname/pg/PostgreSQL-10.7” configuration which was compiled without the “—with-systemd” option 2) Configure PostgreSQL to new location mkdir /hostname/pg/PostgreSQL-10.7_2 ./configure --prefix=/hostname/pg/PostgreSQL-10.7_2 make make install mv /hostname/pg/PostgreSQL-10.7 /hostname/pg/PostgreSQL-10.7.OLD mv /hostname/pg/PostgreSQL-10.7_2 /hostname/pg/PostgreSQL-10.7 3) Start PostgreSQL instance Any more suggestion? Dan From: Lu, Dan Sent: Friday, June 07, 2019 8:46 AM To: 'Christopher Browne' ; Tom Lane Cc: Francisco Olarte ; Kelly, Kevin ; pgsql-gene...@postgresql.org Subject: RE: Postgres 10.7 Systemd Startup Issue Thank you so much for pointing that out. I downloaded the binary from https://ftp.postgresql.org/pub/source/v10.7 After download, I did the following. mkdir /hostname/pg/PostgreSQL-10.7_2 ./configure --prefix=/hostname/pg/PostgreSQL-10.7_2 make make install I now included the option mentioned “./configure --prefix=/hostname/pg/PostgreSQL-10.7_2 --with-systemd”. I also saw that in the documentation now “Using Type=notify requires that the server binary was built with configure --with-systemd.”. We will reply back if we run into further issue. Dan From: Christopher Browne [mailto:cbbro...@gmail.com] Sent: Thursday, June 06, 2019 9:19 PM To: Tom Lane mailto:t...@sss.pgh.pa.us>> Cc: Francisco Olarte mailto:fola...@peoplecall.com>>; Kelly, Kevin mailto:kevin.ke...@msx.bala.susq.com>>; pgsql-gene...@postgresql.org<mailto:pgsql-gene...@postgresql.org>; Lu, Dan mailto:dan...@msx.bala.susq.com>> Subject: Re: Postgres 10.7 Systemd Startup Issue On Thu, Jun 6, 2019, 8:19 PM Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: Francisco Olarte mailto:fola...@peoplecall.com>> writes: > On Thu, Jun 6, 2019 at 6:57 PM Kelly, Kevin > mailto:kevin.ke...@sig.com>> wrote: >> We’re attempting to launch postgres via systemd and noticing that when >> invoking via systemctl start postgres.service the prompt never returns. If >> we switch to another tty and check the status it shows as: >> Active: activating (start) since Thu 2019-06-06 09:36:32 EDT; 12min ago >> If we change the type from notify to forking we see the same behavior. The >> instance seems to be up and running just fine, we just never see the active >> (running) status as we have come to expect. > Are you sure you have the postgres.service correctly configured? ( or > you could post the ExecStart/Type config, or the whole service file ). > The type tells systemd how to know the service has finished starting > and is running, notify means it does systemd integration via socket, > with IIRC postgres does not. I might be confusing this with some other issue, but I think PG does support systemd notification if you build it with the --with-systemd configuration option. This is recommended if you're using systemd to launch PG, because systemd's other options for figuring out the service state work substantially less well. Yeah, I was one of the reviewers of the patch for this feature. If the configuration parameter is turned on then Postgres reports in to SystemD once it completes any crash recovery work to indicate that the database service is up. That way, if there was a bunch of WAL needing to be processed, services depending on Postgres will properly get deferred. You need to set up the service type in the SystemD service file to Type=notify in order to get this behavior IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Postgres 10.7 Systemd Startup Issue
Thank you so much for pointing that out. I downloaded the binary from https://ftp.postgresql.org/pub/source/v10.7 After download, I did the following. mkdir /hostname/pg/PostgreSQL-10.7_2 ./configure --prefix=/hostname/pg/PostgreSQL-10.7_2 make make install I now included the option mentioned “./configure --prefix=/hostname/pg/PostgreSQL-10.7_2 --with-systemd”. I also saw that in the documentation now “Using Type=notify requires that the server binary was built with configure --with-systemd.”. We will reply back if we run into further issue. Dan From: Christopher Browne [mailto:cbbro...@gmail.com] Sent: Thursday, June 06, 2019 9:19 PM To: Tom Lane Cc: Francisco Olarte ; Kelly, Kevin ; pgsql-gene...@postgresql.org; Lu, Dan Subject: Re: Postgres 10.7 Systemd Startup Issue On Thu, Jun 6, 2019, 8:19 PM Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: Francisco Olarte mailto:fola...@peoplecall.com>> writes: > On Thu, Jun 6, 2019 at 6:57 PM Kelly, Kevin > mailto:kevin.ke...@sig.com>> wrote: >> We’re attempting to launch postgres via systemd and noticing that when >> invoking via systemctl start postgres.service the prompt never returns. If >> we switch to another tty and check the status it shows as: >> Active: activating (start) since Thu 2019-06-06 09:36:32 EDT; 12min ago >> If we change the type from notify to forking we see the same behavior. The >> instance seems to be up and running just fine, we just never see the active >> (running) status as we have come to expect. > Are you sure you have the postgres.service correctly configured? ( or > you could post the ExecStart/Type config, or the whole service file ). > The type tells systemd how to know the service has finished starting > and is running, notify means it does systemd integration via socket, > with IIRC postgres does not. I might be confusing this with some other issue, but I think PG does support systemd notification if you build it with the --with-systemd configuration option. This is recommended if you're using systemd to launch PG, because systemd's other options for figuring out the service state work substantially less well. Yeah, I was one of the reviewers of the patch for this feature. If the configuration parameter is turned on then Postgres reports in to SystemD once it completes any crash recovery work to indicate that the database service is up. That way, if there was a bunch of WAL needing to be processed, services depending on Postgres will properly get deferred. You need to set up the service type in the SystemD service file to Type=notify in order to get this behavior IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
Postgresql backup via LVM snapshot?
Hello,
Would you be able to confirm for me that a host level LVM snapshot of the
PGDATA directory along with PG_WAL directly via LVM backup is supported way of
backup and restore?
I read about this here:
https://dba.stackexchange.com/questions/145361/backup-standby-database-using-lvm-snapshot
Is it as simple as:
? SELECT pg_start_backup('Begin LVM Backup At xyz');
? Do LVM backup from o/s end to backup PGDATA/PG_WAL
? SELECT pg_stop_backup();
Thanks.
Dan
IMPORTANT: The information contained in this email and/or its attachments is
confidential. If you are not the intended recipient, please notify the sender
immediately by reply and immediately delete this message and all its
attachments. Any review, use, reproduction, disclosure or dissemination of this
message or any attachment by an unintended recipient is strictly prohibited.
Neither this message nor any attachment is intended as or should be construed
as an offer, solicitation or recommendation to buy or sell any security or
other financial instrument. Neither the sender, his or her employer nor any of
their respective affiliates makes any warranties as to the completeness or
accuracy of any of the information contained herein or that this message or any
of its attachments is free of viruses.
RE: Question on binding VIP to Postgresql instance
Thanks Chris! Our Network Admin have the instruction to bind a VIP, but regarding Postgresql what is needed to be done? We don't have to change any parameter? How about "listen_addresses = ', ' # what IP address(es) to listen on;" or we simply use "*"? -Original Message- From: Chris Coutinho [mailto:c.couti...@redstack.nl] Sent: Tuesday, May 07, 2019 8:40 AM To: Lu, Dan ; David G. Johnston ; Alvaro Aguayo Garcia-Rada ; pgsql-gene...@postgresql.org Subject: RE: Question on binding VIP to Postgresql instance I think getting postgres to work with VIP (assuming virtual IP) is not a postgres issue, but rather a server/network issue. Redhat seems to have some documentation on enabling nonlocal binding, which may be useful for you: https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/load_balancer_administration/s1-initial-setup-forwarding-vsa Met vriendelijke groet, REDstack BV Chris Coutinho Researcher/Data Analyst Van: Lu, Dan Verzonden: dinsdag 7 mei 2019 14:12 Aan: David G. Johnston ; Alvaro Aguayo Garcia-Rada ; pgsql-gene...@postgresql.org Onderwerp: Question on binding VIP to Postgresql instance Good day, Would you be able to help direct me to instruction or implementation step to bind a VIP to a Postgresql instance? I did research and understand that Postgresql does support binding of VIP, but I can't find documentation to implement the solution. Any feedback is greatly appreciated. Dan IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses. IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
Question on binding VIP to Postgresql instance
Good day, Would you be able to help direct me to instruction or implementation step to bind a VIP to a Postgresql instance? I did research and understand that Postgresql does support binding of VIP, but I can't find documentation to implement the solution. Any feedback is greatly appreciated. Dan IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
Version 11.2 for SLES 12?
Hello, Do you happen to know when version 11.2 will be available for download via rpm option? https://www.postgresql.org/download/linux/suse/ [cid:image001.png@01D4C45C.FD0A6820] IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Question on postgresql.conf
Thanks for your reply. I can start the database like this: pg_ctl start -D /hostname/postgres/data -l /hostname/postgres/log My data directory has the following file: postgres.cnf Content of the postgres.cnf contains only 1 line: include /nfs/global/postgres-.cnf Question: Can be a variable like `hostname` derived from Unix shell or I have to hardcode the name of the host like " include /nfs/global/postgres-host123.cnf -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, July 31, 2018 9:52 AM To: Lu, Dan Cc: David G. Johnston ; Alvaro Aguayo Garcia-Rada ; pgsql-gene...@postgresql.org Subject: Re: Question on postgresql.conf "Lu, Dan" writes: > Say, I want to put the config file on a share drive so all my postgres > configuration file can be seen in one location. > /nfs/global/postgres-.cnf What I'd do is make each data directory's postgresql.conf contain just this: include /nfs/global/postgres-.cnf and then just start the postmaster without any weird switches. In this way you will avoid breaking PG-specific tools that expect to find the config file at its standard location. regards, tom lane IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Question on postgresql.conf
Is this the correct syntax for starting Postgres using a separate config_file for each host? We like to take advantage of the Unix variable “hostname” to source in the name of the file. Machine1: (notice we are using the `hostname` unix variable) pg_ctl start -D /hostname/postgres/data -l /hostname/postgres/log -o config_file="/nfs/global/postgres-`hostname`.cnf" Machine2: (notice we are using the `hostname` unix variable) pg_ctl start -D /hostname/postgres/data -l /hostname/postgres/log -o config_file="/nfs/global/postgres-`hostname`.cnf" From: Lu, Dan Sent: Tuesday, July 31, 2018 7:28 AM To: 'David G. Johnston' ; Alvaro Aguayo Garcia-Rada Cc: pgsql-gene...@postgresql.org Subject: RE: Question on postgresql.conf Hi David, Which command? Can you give me an example? Say, I want to put the config file on a share drive so all my postgres configuration file can be seen in one location. /nfs/global/postgres-.cnf Example: /nfs/global/postgres-machine1.cnf /nfs/global/postgres-machine2.cnf /nfs/global/postgres-machine3.cnf From: David G. Johnston [mailto:david.g.johns...@gmail.com] Sent: Tuesday, July 31, 2018 1:48 AM To: Alvaro Aguayo Garcia-Rada mailto:aagu...@opensysperu.com>> Cc: pgsql-gene...@postgresql.org<mailto:pgsql-gene...@postgresql.org>; Lu, Dan mailto:dan...@msx.bala.susq.com>> Subject: Re: Question on postgresql.conf On Monday, July 30, 2018, Alvaro Aguayo Garcia-Rada mailto:aagu...@opensysperu.com>> wrote: As far as I know, it's not currently possible. That would be incorrect, you just need to change server startup commands. https://www.postgresql.org/docs/10/static/runtime-config-file-locations.html David J. IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
RE: Question on postgresql.conf
Hi David, Which command? Can you give me an example? Say, I want to put the config file on a share drive so all my postgres configuration file can be seen in one location. /nfs/global/postgres-.cnf Example: /nfs/global/postgres-machine1.cnf /nfs/global/postgres-machine2.cnf /nfs/global/postgres-machine3.cnf From: David G. Johnston [mailto:david.g.johns...@gmail.com] Sent: Tuesday, July 31, 2018 1:48 AM To: Alvaro Aguayo Garcia-Rada Cc: pgsql-gene...@postgresql.org; Lu, Dan Subject: Re: Question on postgresql.conf On Monday, July 30, 2018, Alvaro Aguayo Garcia-Rada mailto:aagu...@opensysperu.com>> wrote: As far as I know, it's not currently possible. That would be incorrect, you just need to change server startup commands. https://www.postgresql.org/docs/10/static/runtime-config-file-locations.html David J. IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
Question on postgresql.conf
Hello PostgreSQL Support Admin, My name is Dan Lu. I am fairly new to PostgreSQL. I have experience working with Oracle/MySQL database. I am going through the PostgreSQL manual and came across a question hoping you can help me. It appears the "postgresql.conf" file is needed by default to start Postgres. Since we have standard with other RDBMS to store the configuration file on a shared location for easy comparison, I would like to use a different name for this file like .conf. Is this supported? I remember coming across a writing that this is possible only when we are in a postgres prompt like. Would you be able to help me out and can give me an example of how this can be done? Thanks. Dan IMPORTANT: The information contained in this email and/or its attachments is confidential. If you are not the intended recipient, please notify the sender immediately by reply and immediately delete this message and all its attachments. Any review, use, reproduction, disclosure or dissemination of this message or any attachment by an unintended recipient is strictly prohibited. Neither this message nor any attachment is intended as or should be construed as an offer, solicitation or recommendation to buy or sell any security or other financial instrument. Neither the sender, his or her employer nor any of their respective affiliates makes any warranties as to the completeness or accuracy of any of the information contained herein or that this message or any of its attachments is free of viruses.
