Re: [GENERAL] postgresql "init script" for postgres 9.2.15
On 07/13/2016 02:35 PM, Steve Langlois wrote: Thank you but in our appliance, we are not running postgres as a service, we are running it programatically with this script, call postmaster to start it for instance instead of using the service framework. Is there an equivalent in 9.x or does it now have to run as a service. I was able to modify the script to get 9.2 to run but I was hoping this script had been updated for 9.x. The current script uses --auth='ident sameuser' when calling initdb for instance which is not supported in 9.2. Well first: systemctl enable postgresql-9.4.service would make it a service that starts at boot. If you just want to start and stop on demand then: systemctl start postgresql-9.4.service systemctl stop postgresql-9.4.service If you set on using init.d/ scripts then: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=contrib/start-scripts/linux;hb=e9dca8ce147f32d7d64a9e64f9d8339310ad6535 Steve steve.langl...@tavve.com *From:* Adrian Klaver *Sent:* Wednesday, July 13, 2016 5:11:24 PM *To:* Steve Langlois; pgsql-general@postgresql.org *Subject:* Re: [GENERAL] postgresql "init script" for postgres 9.2.15 On 07/13/2016 01:56 PM, Steve Langlois wrote: Hi, I've been searching for a 9.2.15 version of the postgresql script for "init script for starting up the PostgreSQL". I have managed to find older versions than what we are currently using, 8.2.5 but haven't had any luck finding a new version in the postgres 9.2.15 rpms. We are moving from CentOS 5 to CentOS 7 and need to update postgres from 8.2.5 to 9.2.15. Below is the version I have. Where can I find a version of this script for 9.2.15? Thank you. Given that it is Centos 7 they have moved to systemd: https://wiki.postgresql.org/wiki/YUM_Installation#Startup -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore out of memory
I wrote: > I'm still suspicious that this might be some sort of NOTICE-processing- > related buffer bloat. Could you try loading the data with the server's > log_min_messages level cranked down to NOTICE, so you can see from the > postmaster log whether any NOTICEs are being issued to the pg_restore > session? BTW, I experimented with that theory by creating a table with a BEFORE INSERT trigger function that emits a NOTICE, and then making pg_restore restore a lot of data into it. I could not see any memory growth in the pg_restore process. However, I was testing 9.1.22, not 9.1.8. Also, some of the misbehaviors we've discovered along these lines have been timing-sensitive, meaning that the problem might or might not reproduce for another person even with the same software version. Are you running pg_restore locally on the same machine as the server, or across a network --- and if the latter, how fast is the network? 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] Tracking IO Queries
Hi all, I got some IO spikes on my master server. But the point is that I was unable to find the query that caused that, because the query didn't take more than 300ms to run (300ms is the time that my alerts are settled)... Is there any way to track those queries? Maybe with pg_stat_statement? I'm using Postgres 9.2. Thanks Patrick
[GENERAL] FTS with more than one language in body and with unknown query language?
Hi, I have a text corpus which contains either German or English docs and I expect queries where I don't know if it's German or English. So I'd like e.g. that a query "forest" matches "forest" in body_en but also "Wald" in body_de. I created a table with attributes body_en and body_de (type "text"). I will use ts_vector/ts_query on the fly (don't need yet an index (attributes)). * Can FTS handle this multilingual situation? * How to setup a text search configuration which e.g. stems en and de words? * Should I create a synonym dictionary which contains word translations en-de instead of synonyms en-en? * Any hints to related work where FTS has been used in a multilingual context? :Stefan -- 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] postgresql "init script" for postgres 9.2.15
Steve Langlois writes: > Thank you but in our appliance, we are not running postgres as a service, we > are running it programatically with this script, call postmaster to start it > for instance instead of using the service framework. Is there an equivalent > in 9.x or does it now have to run as a service. I was able to modify the > script to get 9.2 to run but I was hoping this script had been updated for > 9.x. The current script uses --auth='ident sameuser' when calling initdb for > instance which is not supported in 9.2. You could try looking in the Fedora 15 RPMs --- that was the last non-systemd Fedora release, I believe. It looks like Fedora was on Postgres 9.0.x at that point, so you might still need to do a bit more fiddling to get it to work with PG 9.2; but it'd be closer than scripts intended for 8.2, for sure. Keep in mind that scripts like this are distro-maintained, not maintained by the Postgres core project; and they tend to get a LOT more churn caused by distro-specific issues than churn caused by Postgres changes. It's not really clear to me that you want to do anything except make whatever changes you have to in the scripts you already have. You may spend more time dealing with useless-to-you changes than you save by not doing your own research on what changed in Postgres. 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] postgresql "init script" for postgres 9.2.15
Please don't top-post. On Wed, Jul 13, 2016 at 5:35 PM, Steve Langlois wrote: > Thank you but in our appliance, we are not running postgres as a service, > we are running it programatically with this script, call postmaster to > start it for instance instead of using the service framework. Is there an > equivalent in 9.x or does it now have to run as a service. > It is not surprising that a piece of code no longer required by the package for its main operation has not been maintained (maybe it has, I'm not familiar with the Centos assets). PostgreSQL is capable of being launched in any number of ways. But as you move toward minority status (which initd on Centos 7 seems to be) the wealth of third-party maintained stuff to pick from tends to decrease. David J.
Re: [GENERAL] postgresql "init script" for postgres 9.2.15
Thank you but in our appliance, we are not running postgres as a service, we are running it programatically with this script, call postmaster to start it for instance instead of using the service framework. Is there an equivalent in 9.x or does it now have to run as a service. I was able to modify the script to get 9.2 to run but I was hoping this script had been updated for 9.x. The current script uses --auth='ident sameuser' when calling initdb for instance which is not supported in 9.2. Steve steve.langl...@tavve.com From: Adrian Klaver Sent: Wednesday, July 13, 2016 5:11:24 PM To: Steve Langlois; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgresql "init script" for postgres 9.2.15 On 07/13/2016 01:56 PM, Steve Langlois wrote: > > Hi, I've been searching for a 9.2.15 version of the postgresql script > for "init script for starting up the PostgreSQL". I have managed to find > older versions than what we are currently using, 8.2.5 but haven't had > any luck finding a new version in the postgres 9.2.15 rpms. We are > moving from CentOS 5 to CentOS 7 and need to update postgres from 8.2.5 > to 9.2.15. > > Below is the version I have. Where can I find a version of this script > for 9.2.15? Thank you. Given that it is Centos 7 they have moved to systemd: https://wiki.postgresql.org/wiki/YUM_Installation#Startup -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] pg_restore out of memory
On 7/13/2016 2:11 PM, Miguel Ramos wrote: Yes. Both 9.1.8, I checked right now. 9.1 is up to 9.1.22, thats a lot of bug fixes you're missing. 9.1.8 was released 2013-02-07, 9.1.22 in 2016-05-12 -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore out of memory
Miguel Ramos writes: > So, what does this mean? > Was it the client that aborted? I think I saw that "unexpected message > type 0x58" on other types of interruptions. Yeah, 0x58 is ASCII 'X' which is a Terminate message. Between that and the unexpected-EOF report, it's quite clear that the client side went belly-up, not the server. We still don't know exactly why, but given that pg_restore reports "out of memory" before quitting, there must be some kind of memory leak going on inside pg_restore. > Jul 13 20:10:10 ema postgres[97889]: [867-1] LOG: statement: COPY > positioned_scan (id_dataset, id_acquired_set, sequence_number, > id_scan_dataset, latitude, longitude, height, srid, srid_vertical) FROM stdin; I'm guessing from the column names that you've got some PostGIS data types in this table. I wonder if that's a contributing factor. I'm still suspicious that this might be some sort of NOTICE-processing- related buffer bloat. Could you try loading the data with the server's log_min_messages level cranked down to NOTICE, so you can see from the postmaster log whether any NOTICEs are being issued to the pg_restore session? 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] pg_restore out of memory
On 07/13/2016 01:51 PM, Miguel Ramos wrote: Finally, here are the log messages at the moment of the error. It is clearly not while building indices. The table in question is a big one, 111GB. Fields latitude, longitude and height are arrays of length around 500- 700 on each row (double and real). So, what does this mean? Was it the client that aborted? I think I saw that "unexpected message type 0x58" on other types of interruptions. So where are you running the pg_restore from, manually from command line or from within a script? Jul 13 20:10:10 ema postgres[97889]: [867-1] LOG: statement: COPY positioned_scan (id_dataset, id_acquired_set, sequence_number, id_scan_dataset, latitude, longitude, height, srid, srid_vertical) FROM stdin; Jul 13 20:10:10 ema postgres[97889]: [867-2] Jul 13 21:08:06 ema postgres[97889]: [868-1] ERROR: unexpected message type 0x58 during COPY from stdin Jul 13 21:08:06 ema postgres[97889]: [868-2] CONTEXT: COPY positioned_scan, line 2779323 Jul 13 21:08:06 ema postgres[97889]: [868-3] STATEMENT: COPY positioned_scan (id_dataset, id_acquired_set, sequence_number, id_scan_dataset, latitude, longitude, height, srid, srid_vertical) FROM stdin; Jul 13 21:08:06 ema postgres[97889]: [868-4] Jul 13 21:08:06 ema postgres[97889]: [869-1] LOG: could not send data to client: Broken pipe Jul 13 21:08:06 ema postgres[97889]: [869-2] STATEMENT: COPY positioned_scan (id_dataset, id_acquired_set, sequence_number, id_scan_dataset, latitude, longitude, height, srid, srid_vertical) FROM stdin; Jul 13 21:08:06 ema postgres[97889]: [869-3] Jul 13 21:08:06 ema postgres[97889]: [870-1] LOG: unexpected EOF on client connection Thanks, -- Miguel A Ter, 12-07-2016 às 15:10 -0400, Tom Lane escreveu: Miguel Ramos writes: This because I have the impression that it is during index creation, where I think client role would be minimal. Hard to believe really, given the spelling of the message. But anyway, be sure you do the run with log_statement = all so that it's clear what is being worked on when the error happens. regards, tom lane -- 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] pg_restore out of memory
Yes. Both 9.1.8, I checked right now. -- Miguel A Qua, 13-07-2016 às 13:59 -0700, John R Pierce escreveu: > On 7/13/2016 1:51 PM, Miguel Ramos wrote: > > Finally, here are the log messages at the moment of the error. > > It is clearly not while building indices. > > > > The table in question is a big one, 111GB. > > Fields latitude, longitude and height are arrays of length around > > 500- > > 700 on each row (double and real). > > > > So, what does this mean? > > Was it the client that aborted? I think I saw that "unexpected > > message > > type 0x58" on other types of interruptions. > > is pg_restore, and the postgres server all the same version? > > $ pg_restore --version > pg_restore (PostgreSQL) 9.3.13 > > $ su - postgres > -bash-4.1$ psql -c "select version()" > version > --- > - > PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu, compiled by gcc > (GCC) > 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit > (1 row) > > > -- > john r pierce, recycling bits in santa cruz > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql "init script" for postgres 9.2.15
On 07/13/2016 01:56 PM, Steve Langlois wrote: Hi, I've been searching for a 9.2.15 version of the postgresql script for "init script for starting up the PostgreSQL". I have managed to find older versions than what we are currently using, 8.2.5 but haven't had any luck finding a new version in the postgres 9.2.15 rpms. We are moving from CentOS 5 to CentOS 7 and need to update postgres from 8.2.5 to 9.2.15. Below is the version I have. Where can I find a version of this script for 9.2.15? Thank you. Given that it is Centos 7 they have moved to systemd: https://wiki.postgresql.org/wiki/YUM_Installation#Startup -- 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] pg_restore out of memory
A Ter, 12-07-2016 às 13:08 +, Sameer Kumar escreveu: > On Tue, 12 Jul 2016, 7:25 p.m. Miguel Ramos, > wrote: > > I found two relevant threads on the mailing-lists. > > The most recent one sugested that postgresql was being configured > > to use > > more memory than what's available. > > The older one sugested that the system limits on the size of the > > data or > > stack segments were lower than required. > > > > So here are some server parameters (relevant or otherwise): > > > > > max_connections = 100 > > > shared_buffers = 4GB -- 25% of RAM > > > temp_buffers = 32MB -- irrelevant? > > > work_mem = 64MB > > > maintenance_work_mem = was 1G lowered to 256M then 64M > > > Why did you lower it? I think increasing it should help better. But > 1GB seems like fine. The advise was on that thread and maybe the problem was very different. The idea I get is that PostgreSQL can always find a way to do its work, maybe using an out of core algorithm. If you tell it to use a lot of memory, then it will try to use RAM and then it really may run out of memory. So, basically, increasing the memory available is a performance improvement, if you feel safe that the memory really is available. But maybe that logic applies only to work_mem... And it's also work_mem that is difficult to bound, according to the manual. I don't really know... > > > wal_buffers = -1 -- should mean 1/32 of shared_buffers = 128MB > > Increase this during the restore, may be 512MB I retain the advise, but now I have posted the log messages to the list. > > > checkpoint_segments = 64 -- WAL segments are 16MB > > > effective_cache_size = 8GB -- irrelevant? > > > > > > I suspect that the restore fails when constructing the indices. > > After > > the process is aborted, the data appears to be all or most there, > > but no > > indices. > > What is logged in database log files? Have you checked that? This time I collected the logs. I posted the 10 relevant lines as a reply to another message. I'll repeat only the ERROR line here: Jul 13 21:08:06 ema postgres[97889]: [868-1] ERROR: unexpected message type 0x58 during COPY from stdin > What are your vm.dirty_ratio and vm.dirty_background_ratio? I think > reducing them may help. But can not really say what exactly would > help unless you are able to get the error source in db logs This is a FreeBSD server. I'm not really sure what the equivalent would be. Also, I don't think tunning the VM would help. This is quite a deterministic abort, 12-13 hours after the beginning of the restore, and does not change much whether it is done during the night or during the day with 10 people working intensively. Thanks, -- Miguel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore out of memory
On 7/13/2016 1:51 PM, Miguel Ramos wrote: Finally, here are the log messages at the moment of the error. It is clearly not while building indices. The table in question is a big one, 111GB. Fields latitude, longitude and height are arrays of length around 500- 700 on each row (double and real). So, what does this mean? Was it the client that aborted? I think I saw that "unexpected message type 0x58" on other types of interruptions. is pg_restore, and the postgres server all the same version? $ pg_restore --version pg_restore (PostgreSQL) 9.3.13 $ su - postgres -bash-4.1$ psql -c "select version()" version PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit (1 row) -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql "init script" for postgres 9.2.15
Hi, I've been searching for a 9.2.15 version of the postgresql script for "init
script for starting up the PostgreSQL". I have managed to find older versions
than what we are currently using, 8.2.5 but haven't had any luck finding a new
version in the postgres 9.2.15 rpms. We are moving from CentOS 5 to CentOS 7
and need to update postgres from 8.2.5 to 9.2.15.
Below is the version I have. Where can I find a version of this script for
9.2.15? Thank you.
#!/bin/sh
# postgresql This is the init script for starting up the PostgreSQL
# server
#
# chkconfig: - 64 36
# description: Starts and stops the PostgreSQL backend daemon that handles \
# all database requests.
# processname: postmaster
# pidfile: $TV_HOME/run/postmaster.pid
# Version 6.5.3-2 Lamar Owen
# Added code to determine if PGDATA exists, whether it is current version
# or not, and initdb if no PGDATA (initdb will not overwrite a database).
# Version 7.0 Lamar Owen
# Added logging code
# Changed PGDATA.
# Version 7.0.2 Trond Eivind Glomsrd
# use functions, add conditional restart
# Version 7.0.3 Lamar Owen
# Check for the existence of functions before blindly using them
# in particular -- check for success () and failure () before using.
# More Cross-distribution support -- PGVERSION variable, and docdir checks.
# Version 7.1 Release Candidate Lamar Owen
# initdb parameters have changed.
# Version 7.1.2 Trond Eivind Glomsrd
# Specify shell for su
# Handle stop better - kill unwanted output, make it wait until the database is
ready
# Handle locales slightly differently - always using "C" isn't a valid option
# Kill output from database initialization
# Mark messages for translation
# Version 7.1.2-2.PGDG Lamar Owen
# sync up.
# Karl's fixes for some quoting issues.
# Version 7.2b2 Lamar Owen
# version change.
# Version 7.2 final. Lamar Owen
# reload from Peter E.
# Eliminate the pidof postmaster test in stop -- we're using pg_ctl so we don't
need pidof.
# Tested the $? return for the stop script -- it does in fact propagate.
# TODO: multiple postmasters.
# Version 7.3 Lamar Owen
# Multiple postmasters, courtesy Karl DeBisschop
# Version 7.4 Lamar Owen.
# Version 7.4.3 Tom Lane
# Support condstop for uninstall
# Minor other changes suggested by Fernando Nasser.
# Version 7.4.5 Tom Lane
# Rewrite to start postmaster directly, rather than via pg_ctl; this avoids
# fooling the postmaster's stale-lockfile check by having too many
# postgres-owned processes laying about.
# Version 8.1 Devrim Gunduz
# Increased sleep time from 1 sec to 2 sec.
# Version 8.2 Devrim Gunduz
# Set initdb as a seperate option.
. ${TV_ENV:-/usr/}/conf/environ.conf
TV_DB=${TV_DB:-/usr//databases}
TV_PG_DEBUG=${TV_PG_DEBUG:-/dev/null}
# PGVERSION is the full package version, e.g., 8.2.0
# Note: the specfile ordinarily updates this during install
PGVERSION=8.2.5
# PGMAJORVERSION is major version, e.g., 8.2 (this should match PG_VERSION)
PGMAJORVERSION=`echo "$PGVERSION" | sed 's/^\([0-9]*\.[0-9]*\).*$/\1/'`
# Source function library.
. /etc/rc.d/init.d/functions
# Get function listing for cross-distribution logic.
TYPESET=`typeset -f|grep "declare"`
# Get config.
. /etc/sysconfig/network
# Find the name of the script
NAME=`basename $0`
if [ ${NAME:0:1} = "S" -o ${NAME:0:1} = "K" ]
then
NAME=${NAME:3}
fi
# For SELinux we need to use 'runuser' not 'su'
if [ -x /sbin/runuser ]
then
SU=runuser
else
SU=su
fi
# Set defaults for configuration variables
PGENGINE=/usr/bin
PGPORT=5432
PGDATA=$TV_DB/pgsql
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base/template1" ]
then
echo "Using old-style directory structure"
else
PGDATA=$TV_DB/pgsql/data
fi
PGLOG=${TV_LOG:-/usr//log}/pgstartup.log
# Override defaults from /etc/sysconfig/pgsql if file is present
[ -f ${TV_CONF}/${NAME} ] && . ${TV_CONF}/${NAME}
export PGDATA
export PGPORT
# Check that networking is up.
# Pretty much need it for postmaster.
[ "${NETWORKING}" = "no" ] && exit 1
[ -f "$PGENGINE/postmaster" ] || exit 1
script_result=0
start(){
PSQL_START=$"Starting ${NAME} service: "
# Make sure startup-time log file is valid
if [ ! -e "$PGLOG" -a ! -h "$PGLOG" ]
then
touch "$PGLOG" || exit 1
# chown postgres:postgres "$PGLOG"
chmod go-rwx "$PGLOG"
# do we need this?
# [ -x /usr/bin/chcon ] && /usr/bin/chcon -u system_u -r object_r -t
postgresql_log_t "$PGLOG" 2>/dev/null
fi
# Check for the PGDATA structure
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base" ]
then
# Check version of existing PGDATA
if [ x`cat "$PGDATA/PG_VERSION"` != x"$PGMAJORVERSION" ]
then
SYSDOCDIR="(Your System's documentation directory)"
if [ -d "/usr/doc/postgresql-$PGVERSION" ]
then
SYSDOCDIR=/usr/doc
fi
if [ -d "/usr/share/doc/postgresql-$PGVERSION" ]
then
SYSDOCDIR=/usr/share/doc
fi
if [ -d "/usr/doc/packages/postgresql-$PGVERSION" ]
then
SYSDOCDIR=/usr/doc/packages
fi
if [ -d "/usr/share/doc/packages/postgresql-$PGVERSION" ]
then
SYSDOCDIR=/usr/share/doc/packages
fi
echo
ec
Re: [GENERAL] pg_restore out of memory
Finally, here are the log messages at the moment of the error. It is clearly not while building indices. The table in question is a big one, 111GB. Fields latitude, longitude and height are arrays of length around 500- 700 on each row (double and real). So, what does this mean? Was it the client that aborted? I think I saw that "unexpected message type 0x58" on other types of interruptions. Jul 13 20:10:10 ema postgres[97889]: [867-1] LOG: statement: COPY positioned_scan (id_dataset, id_acquired_set, sequence_number, id_scan_dataset, latitude, longitude, height, srid, srid_vertical) FROM stdin; Jul 13 20:10:10 ema postgres[97889]: [867-2] Jul 13 21:08:06 ema postgres[97889]: [868-1] ERROR: unexpected message type 0x58 during COPY from stdin Jul 13 21:08:06 ema postgres[97889]: [868-2] CONTEXT: COPY positioned_scan, line 2779323 Jul 13 21:08:06 ema postgres[97889]: [868-3] STATEMENT: COPY positioned_scan (id_dataset, id_acquired_set, sequence_number, id_scan_dataset, latitude, longitude, height, srid, srid_vertical) FROM stdin; Jul 13 21:08:06 ema postgres[97889]: [868-4] Jul 13 21:08:06 ema postgres[97889]: [869-1] LOG: could not send data to client: Broken pipe Jul 13 21:08:06 ema postgres[97889]: [869-2] STATEMENT: COPY positioned_scan (id_dataset, id_acquired_set, sequence_number, id_scan_dataset, latitude, longitude, height, srid, srid_vertical) FROM stdin; Jul 13 21:08:06 ema postgres[97889]: [869-3] Jul 13 21:08:06 ema postgres[97889]: [870-1] LOG: unexpected EOF on client connection Thanks, -- Miguel A Ter, 12-07-2016 às 15:10 -0400, Tom Lane escreveu: > Miguel Ramos writes: > > This because I have the impression that it is during index > > creation, > > where I think client role would be minimal. > > Hard to believe really, given the spelling of the message. But > anyway, > be sure you do the run with log_statement = all so that it's clear > what > is being worked on when the error happens. > > 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] Database Architect - Voleon Capital Management LP
Database Architect Berkeley IT Full-time The Voleon Group is a technology-driven investment firm employing cutting-edge machine learning techniques seeks an exceptionally capable Database Architect. Your responsibilities will include making technical decisions with respect to relational database technologies; deploying, configuring and maintaining database systems; working with software engineers to work through performance problems and optimize database performance; and support database replication and backup processes. The firm researches and deploys systematic trading strategies designed to generate attractive returns independent of the performance of the overall market. Join a team of under 50 people that includes Ph.D.’s from Berkeley, Chicago, CMU, Princeton, Stanford, and UCLA, led by the founder and CEO of a successful Internet infrastructure technology firm. The firm’s offices are walking distance from BART and the UC Berkeley campus in downtown Berkeley, California. We have a casual and collegial office environment, daily catered lunches, and competitive benefits packages. We seek candidates with a passion for building out automated, maintainable infrastructure that is flexible in serving the needs of a dynamic team of software, research and systems engineers. Someone with an eye for detail, and who has a solid grasp of the revolution currently happening within the IT and database administration community. We are growing rapidly. Willingness to take initiative and a gritty determination to productize are essential. Required qualifications Computer science degree or equivalent 5+ years professional experience in IT, 2+ years as a DBA Expert in Postgres, including replication and performance optimization. Strong experience managing Oracle deployments, including backup and recovery. Professional experience writing and debugging SQL. Professional experience deploying and maintaining distributed key/value stores, such as Redis or Cassandra. Strong proficiency in storage technologies, SAN/NAS, RAID systems, etc. Preferred qualifications Professional experience with MySQL and SQL Server. Database access control, RBAC. Solid experience with Windows Server and SQL Server. Scripting skills including Python and PL/SQL. If interested, apply at http://voleon.com/apply/. -- View this message in context: http://postgresql.nabble.com/Database-Architect-Voleon-Capital-Management-LP-tp5911824.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pglogical cascading replication (chaining replication)
On 07/12/2016 07:20 AM, Nick Babadzhanian wrote:
I apologize if this is wrong place to ask the question.
A quote from pglogical FAQ:
Q. Does pglogical support cascaded replication?
Subscribers can be configured as publishers as well thus cascaded replication
can be achieved
by forwarding/chaining (again no failover though).
The only mentions of forwarding on documentation page are:
Cascading replication is implemented in the form of changeset forwarding.
forward_origins - array of origin names to forward, currently only supported
values are empty
array meaning don't forward any changes that didn't originate on provider node, or
"{all}"
which means replicate all changes no matter what is their origin, default is
"{all}"
So my question is how to forward changeset using pglogical?
That's a great question. I have tried to figure out that information as
well. Unfortunately it appears that it is locked up in 2ndQuadrant's
head. I suggest contacting them.
Sincerely,
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
--
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] pglogical cascading replication (chaining replication)
After checking logs I noticed this error on p3-node:
>ERROR: cache lookup failed for replication origin
>'pgl_test_node_p1_provider_sub_p1_t06410f8'
pgl_test_node_p1_provider_sub_p1_t06410f8 is origin for p1-node.
Here are the logs from all three server (this happens every time I insert
something into p1 table):
p1:
>LOG: starting pglogical supervisor
>LOG: starting pglogical database manager for database test
>LOG: starting logical decoding for slot
>"pgl_test_node_p1_provider_sub_p1_t06410f8"
>DETAIL: streaming transactions committing after 0/3BDBFD0, reading WAL from
>0/3BDBFD0
>LOG: logical decoding found consistent point at 0/3BDBFD0
>DETAIL: There are no running transactions.
p2:
>LOG: starting pglogical supervisor
>LOG: starting pglogical database manager for database test
>LOG: starting apply for subscription sub_p1_to_p2_insert_only
>LOG: starting logical decoding for slot
>"pgl_test_node_p2_9face77_sub_p2_t6fd19a3"
>DETAIL: streaming transactions committing after 0/35DD958, reading WAL from
>0/35DD958
>LOG: logical decoding found consistent point at 0/35DD958
>DETAIL: There are no running transactions.
>LOG: starting logical decoding for slot
>"pgl_test_node_p2_9face77_sub_p2_t6fd19a3"
>DETAIL: streaming transactions committing after 0/35DDA38, reading WAL from
>0/35DDA00
>LOG: logical decoding found consistent point at 0/35DDA00
>DETAIL: There are no running transactions.
>LOG: could not receive data from client: Connection reset by peer
>LOG: unexpected EOF on standby connection
>LOG: starting logical decoding for slot
>"pgl_test_node_p2_9face77_sub_p2_t6fd19a3"
>DETAIL: streaming transactions committing after 0/35DDA38, reading WAL from
>0/35DDA00
>LOG: logical decoding found consistent point at 0/35DDA00
>DETAIL: There are no running transactions.
>LOG: could not receive data from client: Connection reset by peer
>LOG: unexpected EOF on standby connection
p3:
>LOG: starting pglogical supervisor
>LOG: starting pglogical database manager for database test
>LOG: starting apply for subscription sub_p2_to_p3_insert_only
>ERROR: cache lookup failed for replication origin
>'pgl_test_node_p1_provider_sub_p1_t06410f8'
>LOG: worker process: pglogical apply 13294:1876007473 (PID 14180) exited with
>exit code 1
>LOG: starting apply for subscription sub_p2_to_p3_insert_only
>ERROR: cache lookup failed for replication origin
>'pgl_test_node_p1_provider_sub_p1_t06410f8'
>LOG: worker process: pglogical apply 13294:1876007473 (PID 14189) exited with
>exit code 1
- Original Message -
From: "Nick Babadzhanian"
To: "pgsql-general"
Sent: Tuesday, July 12, 2016 5:20:59 PM
Subject: pglogical cascading replication (chaining replication)
I apologize if this is wrong place to ask the question.
A quote from pglogical FAQ:
> Q. Does pglogical support cascaded replication?
> Subscribers can be configured as publishers as well thus cascaded replication
> can be achieved
> by forwarding/chaining (again no failover though).
The only mentions of forwarding on documentation page are:
> Cascading replication is implemented in the form of changeset forwarding.
> forward_origins - array of origin names to forward, currently only supported
> values are empty
> array meaning don't forward any changes that didn't originate on provider
> node, or "{all}"
> which means replicate all changes no matter what is their origin, default is
> "{all}"
So my question is how to forward changeset using pglogical?
Here's my setup:
There are 3 identical CentOS 7 servers: p1, p2 and p3. Postgres version is
9.5.3.
p1:
select pglogical.create_node
(
node_name := 'node_p1_provider',
dsn := 'host=192.168.1.101 port=5432 dbname=test'
);
select pglogical.replication_set_add_all_tables('default_insert_only',
array['public']);
p2:
select pglogical.create_node(
node_name := 'node_p2_provider_and_subscriber',
dsn := 'host=192.168.1.102 port=5432 dbname=test'
);
select pglogical.replication_set_add_all_tables('default_insert_only',
array['public']);
select pglogical.create_subscription
(
subscription_name => 'sub_p1_to_p2_insert_only',
provider_dsn => 'host=192.168.1.101 port=5432 dbname=test',
replication_sets => array['default_insert_only']
);
p3:
select pglogical.create_node(
node_name := 'node_p3_subscriber',
dsn := 'host=192.168.1.103 port=5432 dbname=test'
);
select pglogical.create_subscription
(
subscription_name => 'sub_p2_to_p3_insert_only',
provider_dsn => 'host=192.168.1.102 port=5432 dbname=test',
replication_sets => array['default_insert_only']
);
Result:
p1:
insert into public.test (col1) values (1);
select count(1) from public.test; -- returns 1;
p2:
insert into public.test (col1) values (2);
select count(1) from public.test; -- returns 2;
p3:
select count(1) from public.test; -- returns 1;
Expected:
p3 recieves all inserts, thus the count on p3 should be 2 (same as on p2).
--
Sent via pg
Re: [GENERAL] Pg_bulkload for PostgreSql 9.5
Hi, On Fri, 2016-06-17 at 13:29 +0200, Job wrote: > i have some problems about compiling pg_bulkload-3.1.8 on a CentOS 5 with > Postgresql 9.5. > If i use a previous version of Psql it compile and works. I just built and pushed 3.1.9 packages to yum repo for CentOS 5. They will sync to master repo in an hour: https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-5-x86_64/ You can download and use it via RPMs. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ 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] jdbc 9.4-1208 driver for PostgreSQL 9.5?
Joek Hondius wrote: > (I hope i am on the right list) pgsql-jdbc would have been the perfect list. > jdbc.postgresql.org lists version 9.4 build 1208 as the lastest. > Is this the correct version to use with PostgreSQL 9.5 (or even 9.6-beta)? > I cannot find info on this elsewhere. Yes, you should just use the latest driver. Don't be worried if they don't have the same version number. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] jdbc 9.4-1208 driver for PostgreSQL 9.5?
Hi, (I hope i am on the right list) jdbc.postgresql.org lists version 9.4 build 1208 as the lastest. Is this the correct version to use with PostgreSQL 9.5 (or even 9.6-beta)? I cannot find info on this elsewhere. https://jdbc.postgresql.org/download.html Greetings Joek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore out of memory
On Tue, Jul 12, 2016 at 12:25:08PM +0100, Miguel Ramos wrote: > > # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump > > pg_restore: [custom archiver] out of memory > > 12:09:56.58 9446.593u+1218.508s 24.3% 167+2589k 6+0io 0pf+0sw 6968822cs ... > I suspect that the restore fails when constructing the indices. After the > process is aborted, the data appears to be all or most there, but no > indices. ... > I don't know what else to try. You could try restoring w/o indices and re-adding them later. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
