Re: [GENERAL] problem with query
hi giuseppe, On Fri, Sep 13, 2013 at 11:49 AM, Giuseppe Broccolo < giuseppe.brocc...@2ndquadrant.it> wrote: > > The problem is the encoding: SQL_ASCII encodes only the first 128 > characters, so 'ñ' and 'Ñ' cannot be encoded in ASCII. If you insert text > 'ñ' or 'Ñ' in a table inside a database (e.g. 'ascii_test') with SQL_ASCII > encoding, they will be seen as two distinct characters: > > > Hexadecimals 'c3', 'b1' and '91' does not decode anything in ASCII, even > if terminal show pairs as 'ñ' or 'Ñ'. So ILIKE operator cannot apply case > insensitive pattern matching to data which does not encode any string (in > the SQL_ASCII encoding) and works as a normal LIKE. > > Even if the client which insert data has 8-bit encoding (UTF8, where 'ñ' > and 'Ñ' are decoded), SQL_ASCII database cannot convert strings anyway, and > problem remains. > > The best way is to work with a UTF8 encoded database. Is there a > particular reason to work with SQL_ASCII encoding? > > no, the only reason until now has been "If it ain't broke, don't fix it."... so maybe it is time to start thinking in migrate the encoding to utf8... is there any normal procedure to do this? dump and then restore in a freshly initiated database should be enough? thanks! -- Roberto Scattini
Re: [GENERAL] problem with query
On Thu, Sep 12, 2013 at 6:49 PM, Chris Curvey wrote: > >> > Is this just a case-sentitvity issue? if personas.apellido is a varchar > field, then I think that's your trouble. (it would have to be citext in > order for "nunez" = "NUNEZ".) > > > yes, is just a case-sensitivity issue. yes, is a varchar field. but what i dont understand is why the problem of "insensitivity" is only with ñ (lower). i mean, if i remove my ñ (and subsecuent chars), both querys return the same rows: pgon=# set client_encoding = SQL_ASCII; SET pgon=# SELECT DISTINCT p.id, p.apellido, p.nombre FROM personal.personas AS p WHERE p.apellido ilike '%NU%'; id |apellido| nombre ---++-- 39489 | NUÑEZ | JUAN 39937 | PEREZ NUÑEZ| FRANCISCO 40229 | PANNUNZIO | MARIA CRISTINA 39453 | GUARNUCIO | CARLA GISELLE 39498 | NUÑEZ ESQUIBEL | RICARDO RODOLFO 40326 | MANUZZA| ANDREA ALEJANDRA 40205 | NIETO FRAGNUL | LAUTARO ALFREDO 40220 | NUÑEZ VERA | MANUEL SANTIAGO (8 rows) pgon=# SELECT DISTINCT p.id, p.apellido, p.nombre FROM personal.personas AS p WHERE p.apellido ilike '%nu%'; id |apellido| nombre ---++-- 39489 | NUÑEZ | JUAN 39937 | PEREZ NUÑEZ| FRANCISCO 40229 | PANNUNZIO | MARIA CRISTINA 39453 | GUARNUCIO | CARLA GISELLE 39498 | NUÑEZ ESQUIBEL | RICARDO RODOLFO 40326 | MANUZZA| ANDREA ALEJANDRA 40205 | NIETO FRAGNUL | LAUTARO ALFREDO 40220 | NUÑEZ VERA | MANUEL SANTIAGO (8 rows) what makes the 'ñ' char special that makes the queries the same when it is not there? -- Roberto Scattini
Re: [GENERAL] problem with query
On Thu, Sep 12, 2013 at 7:02 PM, Bosco Rama wrote: > On 09/12/13 14:49, Chris Curvey wrote: > > Is this just a case-sentitvity issue? if personas.apellido is a varchar > > field, then I think that's your trouble. (it would have to be citext in > > order for "nunez" = "NUNEZ".) > > He's using 'ilike' in his query, so this is more likely to be a > locale/charset/collation/encoding issue. > > Roberto, what encodings are in use by the client and the server/DB? > > hi bosco, in fact i believe that i have that problem... but i cant undestand why and how to fix it. the database has SQL_ASCII encoding, and my client... i am not sure, when i connect directly with psql from localhost i must set client encoding to SQL_ASCII or i receive an "invalid utf8 sequence" error... but the problem is there, from psql, from pgadmin and from php/apache (my webservers) thanks -- Roberto Scattini
Re: [GENERAL] problem with query
On Thu, Sep 12, 2013 at 6:22 PM, Chris Curvey wrote: > > > On Thu, Sep 12, 2013 at 4:34 PM, Roberto Scattini < > roberto.scatt...@gmail.com> wrote: >> >> >> we are using postgresql 9.1 from ubuntu packages and the database >> encoding is (sadly) SQL_ASCII >> >> can anybody point me in the right direction to solve this issue? >> >> -- >> Roberto Scattini >> > > If you could tells what you are expecting, and what you are actually > getting, that would be helpful. > > hi chris, sorry, the query with ilike '%NUÑEZ%' is returning 4 rows and the query with ilike '%nuñez%' is returning 0 rows. a simplified version of the query with his results: pgn=# set client_encoding = SQL_ASCII; SET pgon=# SELECT DISTINCT p.id, p.apellido, p.nombre FROM personal.personas AS p WHERE p.apellido ilike '%nuñez%'; id | apellido | nombre +--+ (0 rows) pgon=# SELECT DISTINCT p.id, p.apellido, p.nombre FROM personal.personas AS p WHERE p.apellido ilike '%NUÑEZ%'; id |apellido| nombre ---++- 39489 | NUÑEZ | JUAN 39937 | PEREZ NUÑEZ| FRANCISCO 39498 | NUÑEZ ESQUIBEL | RICARDO RODOLFO 40220 | NUÑEZ VERA | MANUEL SANTIAGO (4 rows) thanks! -- Roberto Scattini
[GENERAL] problem with query
hi, today we discovered that this query doesn't return the expected values: SELECT DISTINCT p.id, p.apellido AS "Apellido", p.nombre AS "Nombre", pf.nombre AS "Funcion", to_char(da.f_ingreso_pg, 'dd/mm/') AS "Fecha Ingreso PG", e.estado AS "Estado", to_char(pe.fecha, 'dd/mm/') AS "Fecha Estado" FROM personal.personas AS p LEFT JOIN personal.personas_da_pg AS da ON p.id=da.id_persona LEFT JOIN personal.personas_estado AS pe ON da.estado_id=pe.id LEFT JOIN personal.estados AS e ON pe.id_estado=e.id LEFT JOIN procu_departamento AS d ON d.id=da.id_departamento LEFT JOIN procu_departamento AS dto ON left(d.c_organigrama, 4)||'000'=dto.c_organigrama LEFT JOIN procu_departamento AS dir ON left(d.c_organigrama, 3)||''=dir.c_organigrama LEFT JOIN procu_departamento AS dg ON left(d.c_organigrama, 2)||'0'=dg.c_organigrama LEFT JOIN personal.funciones AS pf ON pf.id=da.funcion_id LEFT JOIN personal.profesiones AS pp ON pp.id=p.id_profesion WHERE p.apellido ilike '%nuñez%' ORDER BY "Apellido" the exact same query with ilike '%NUÑEZ%' works OK... we are using postgresql 9.1 from ubuntu packages and the database encoding is (sadly) SQL_ASCII can anybody point me in the right direction to solve this issue? -- Roberto Scattini
Re: [GENERAL] streaming replication question
On Thursday, March 21, 2013, wd wrote: > You can combine warm standby and streaming, we do this in our product database. > When the standby is to far from the master, the slave will try to use restore_command to restore the database(warm standby), when the standby catch up the master, the steaming will working again. > > BTW: we use ominipitr. > ok. tomorrow i will check that again. i think that when restore_command was enabled in the slave, the wal receiver proccess didnt appear (and i didnt see the changes inmediately happening in the slave). can you post your recovery.conf? thanks for the reply! -- Roberto Scattini
Re: [GENERAL] streaming replication question
On Thu, Mar 21, 2013 at 3:45 PM, Roberto Scattini < roberto.scatt...@gmail.com> wrote: > > i mean, i can have both configurations enabled? >> >> > no, i think my setup, with this config, is just doing warm standby. but > the streaming replication was working before adding archive_command to > master and restore_command to slave. > > if i remove restore_command and archive_cleanup_command from recovery.conf on slave the streaming replication works again. so, i cant combine both methods? i must shut down my master to start the streaming replication? this comment confused me: # Specifies a command to load archive segments from the WAL archive. If # wal_keep_segments is a high enough number to retain the WAL segments # required for the standby server, this may not be necessary. But # a large workload can cause segments to be recycled before the standby # is fully synchronized, requiring you to start again from a new base backup. restore_command = 'cp /path_to/archive/%f "%p"' -- Roberto Scattini
Re: [GENERAL] streaming replication question
On Thu, Mar 21, 2013 at 3:32 PM, Roberto Scattini < roberto.scatt...@gmail.com> wrote: > hi list, > > i'm installing a streaming replication master-slave setup in ubuntu 12.04 > LTS, with postgresql 9.1 > > the tutorials and the documentation are a very good start point, but i > have one question related to some fine grained configurations. > > it is said that i should stop the master db in order to make de starting > backup, OR run pg_start_backup and keep the archive files for the slave to > catch up. if i dont do this, maybe the slave stays far behind the master > and the wal_segments arent enough to start the replication. > if I understand this right, i can "combine" the old "warm standby" configs > of archive_command in the master and restore command in the slave to ensure > that the slave will have the necessary info to start the replication. > > > i mean, i can have both configurations enabled? > > no, i think my setup, with this config, is just doing warm standby. but the streaming replication was working before adding archive_command to master and restore_command to slave. i used the info from this site: http://wiki.postgresql.org/wiki/Streaming_Replication -- Roberto Scattini
[GENERAL] streaming replication question
hi list, i'm installing a streaming replication master-slave setup in ubuntu 12.04 LTS, with postgresql 9.1 the tutorials and the documentation are a very good start point, but i have one question related to some fine grained configurations. it is said that i should stop the master db in order to make de starting backup, OR run pg_start_backup and keep the archive files for the slave to catch up. if i dont do this, maybe the slave stays far behind the master and the wal_segments arent enough to start the replication. if I understand this right, i can "combine" the old "warm standby" configs of archive_command in the master and restore command in the slave to ensure that the slave will have the necessary info to start the replication. i mean, i can have both configurations enabled? right now, my streaming replication setup has this configs: recovery.conf (slave) --- standby_mode = 'on' primary_conninfo = 'host=192.168.206.134' trigger_file = '/var/lib/postgresql/9.1/sgi/sgi.trigger' restore_command = '/usr/lib/postgresql/9.1/bin/pg_standby -d -k 100 -s 20 -t /var/lib/postgresql/9.1/sgi.trigger /var/lib/postgresql/9.1/archive %f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/archive %r' --- postgresql.conf (master) --- data_directory = '/var/lib/postgresql/9.1/sgi' hba_file = '/etc/postgresql/9.1/sgi/pg_hba.conf' ident_file = '/etc/postgresql/9.1/sgi/pg_ident.conf' external_pid_file = '/var/run/postgresql/9.1-sgi.pid' listen_addresses = '*' port = 5432 max_connections = 100 unix_socket_directory = '/var/run/postgresql' ssl = true shared_buffers = 24MB wal_level = hot_standby archive_mode = on archive_command = 'rsync -arv %p 192.168.206.133:/var/lib/postgresql/9.1/archive/%f
Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?
On Thu, Oct 21, 2010 at 1:37 PM, Rob Sargent wrote:
>
>
> On 10/21/2010 10:27 AM, Scott Marlowe wrote:
> > On Thu, Oct 21, 2010 at 9:33 AM, Brian Hirt wrote:
> >>>
> >>> There are only two tables in the query.
> >>>
> >>
> >> Tim,
> >>
> >> No, your query is written incorrectly. I don't understand why you come
> on to this list all hostile and confrontational. Regardless, people still
> try to help you and then you still ignore the advice of people that are
> giving you the solutions to your problems.
> >
> > Maybe he's used to paid commercial support where people are often
> > quite rude and hostile to the support staff to try and "motivate" them
> > or something? I've seen it before for sure.
> >
> > Again, OP, what does EXPLAIN say about this query?
> >
>
> Maybe I should re-read, but I didn't feel any confrontation.
> Frustration for sure. OP has clearly tried pretty hard, on some tricky
> bits too, but I'm betting all for naught if (as seems likely) it's just
> mistaken sql. "update from" is NOT straight forward.
>
>
i had the same feeling than brian and scott, but i am one of the
"questioners" not the "answerers" on the list, so i didnt said anything...
but the "i-am-touching-many-parameters-and-doesnt-work" and then complain is
not one of the best strategies...
the SQL sentence is far more complicated than it pretended to be...
--
Roberto Scattini
___ _
))_) __ )L __
((__)(('(( ((_)
[GENERAL] server install recommendations?
hi everybody:
in a couple of days i will reinstall an offline database server. It's
a old HP Proliant DL580 G3 with three disks (147 GB each). Currently
it has a debian Sarge in a RAID5 hardware array ( with HP Smart Array
6i, [RAID bus controller: Compaq Computer Corporation Smart Array 64xx
(rev 01)] ).
i googled a while and get to this page:
http://www.powerpostgresql.com/PerfList , so my first question is,
this document and the tips given hasn't been deprecated by now, right?
then:
-is raid5 the worst election in this scenario (three disks)?
-which is the best (and with that i mean secure in first place and
with more perfomance in second) possible configuration achievable with
this three disks?
-should i ask my boss to buy another disk? (it will be difficult, but
if i can demonstrate It worth it...)
i think i will "Separate the Transaction Log from the Database" with
two RAID1 arrays (if they buy the new disk). is this a good way to go?
it would be too bad if i put the logs in a disk without RAID? (only if
i dont get the new disk...)
our application (running on separate webserver) is of the type "OLTP",
the server will be entirely dedicated to postgresql. i will configure
a warm-standby server, so the WAL files will be forwarded to another
server.
thanks in advance!
--
Roberto Scattini
___ _
))_) __ )L __
((__)(('(( ((_)
--
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] standby questions
On Feb 9, 2008 5:50 AM, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Fri, 8 Feb 2008, David Wall wrote:
>
> > Does pg_standby take care of this by checking file sizes or the like? In my
> > testing with scp, we never experienced any problems, but I wonder if we were
> > somehow "just lucky."
>
> pg_standby only processes files of exactly the length they're supposed to
> be. On Windows it even sleeps a bit after that to give time for things to
> settle.
>
> The main risky situation you could end up in is if you were using a copy
> program that created the whole file at its full size first then wrote the
> data to it. I don't think there are many programs that operate like that
> around and certainly scp doesn't do that.
>
"atomic tool":
The reason rsync is used in the archive_command is that rsync features
an 'atomic copy' - that is, the in-progress destination file is
created as a temp file, and then renamed when the copy is complete. In
the situation above, where segments are archived straight to the
directory that the slave reads from, 'cp' can cause an error whereby
the slave attempts to process a partially-copied WAL segment. If this
happens, postgres will emit an error like:
PANIC: archive file "00010031" has wrong size:
1810432 instead of 16777216
LOG: startup process (PID 11356) was terminated by signal 6
LOG: aborting startup due to startup process failure
taken from http://archives.postgresql.org/sydpug/2006-10/msg1.php
thanks everybody!!
--
Roberto Scattini
___ _
))_) __ )L __
((__)(('(( ((_)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] standby questions
hi list:
im working in the setup of a warm standby server. im using
postgres-8.2.5 in the slave and 8.2.4 in master right now, in
production it will be only 8.2.5, everything in debian etch.
we decided this solution because we want an easy to
migrate/implement/adminstrate backup sever, and we have a few minutes
of data loss (if we can be precise with the "starting point in time"
of that loss).
by now, i have the warm standby server working fine (at least, it logs
that processes the archives...).
if i am undestanding, the "complete" (in broad strokes) process for
WAL archiving and warm standby, is something like this:
1) the primary server processes some transactions and
generates some "WAL segment files" in the $DATA/pg_xlog/ dir of 16MB
each (by default).
2) if archive_command is activated and working, the primary
server sends (preferably with rsync or some other "atomic tool") the
NEW WAL files to the standby server. Later, at some point, the primary
server will delete this files when considers that are not necessary
anymore.
3) the standby server receives the archive files and processes
them somehow (almost everybody does this with pg_standby by now?) and
keep waiting for a new WAL file or the trigger file.
now, as you can see, im not a expert on database tech, maybe only a
"brave admin", so, reading the docs and seeing the warm standby
"working", i have a few questions (in any case, links to docs a
welcome!!):
-a "WAL segment file" is the same that a "log file segment"?
-what are the "log file segments" referenced by checkpoint_segments?
are the "binary logs" where postgres stores lasts transactions, the
ones in $DATA/pg_xlog dir? if this is true, then:
what means "Maximum distance between automatic WAL checkpoints"???
this is how often, in "log file segments", postgres will perform a
checkpoint, generating a special checkpoint record from which to start
the redo operation, dont?
-what is the "restartpoint" named in the "Warm Standby Servers for
High Availability" page?
(http://www.postgresql.org/docs/8.2/static/warm-standby.html) i cant
find a definition in any other doc...
-how often a new WAL file is generated? this depends on the server load?
-in one WAL file i could have one, more than one and even an
incomplete "transaction"?? (where is the doc i need to read?!)
-if i have incomplete transactions on a WAL, how the standby server
processes that? what if the rest of the transaction never reaches the
standby server?
-how do i know exactly at which point in time (in transactions) my
standby server is, if i have to switch to primary role?
-how many archive files is safe to keep in the standby server? right
now, i have "-k 100" in the pg_standby opts but in the pg_standby
README says:
"You should be wary against setting this number too low,
since this may mean you cannot restart the standby. This
is because the last restartpoint marked in the WAL files
may be many files in the past and can vary considerably.
This should be set to a value exceeding the number of WAL
files that can be recovered in 2*checkpoint_timeout seconds,
according to the value in the warm standby postgresql.conf.
It is wholly unrelated to the setting of checkpoint_segments
on either primary or standby."
i cant use the %r because im in 8.2 and not thinking in upgrade by now...
this is related to the restartpoint in my previous question... but i
dont know exactly what it is so i cant make a more detailed analysis.
finally: does anybody have a config like this one working with
heartbeat? is that recommendable?
wow! sorry for the huge post... i think i tied some loose ends just by
writing this email!! :D
thanks in advance!!
--
Roberto Scattini
___ _
))_) __ )L __
((__)(('(( ((_)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] pg_xlog and standby - SOLVED
On Jan 24, 2008 4:30 PM, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Thu, 24 Jan 2008, Erik Jones wrote:
>
> > To disable archiving, set archive_command="", commenting it out won't
> > change it...However, commenting a config is not necessarily equivalent
> > to disabling something.
>
> This is actually a problem that is corrected in the upcoming 8.3 release.
> From those release notes:
>
> "Commenting out a parameter in postgresql.conf now causes it to revert to
> its default value. [In 8.2 and earlier releases] commenting out an entry
> left the parameter's value unchanged until the next server restart."
>
> This is probably why Roberto didn't see the expected behavior until a
> server restart.
>
in fact, googling for the problem i found those release notes that led
me to think that the restart was necessary...
:D
--
Roberto Scattini
___ _
))_) __ )L __
((__)(('(( ((_)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] pg_xlog and standby - SOLVED
On Jan 23, 2008 11:07 PM, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Wed, 23 Jan 2008, Roberto Scattini wrote:
>
> > the problem that im having is that i have A LOT of
> > archive files on pg_xlog dir, and thats because the archive_command
> > keeps failing (the standby server had filled his disk with archives
> > received but not proccesed), so now, i dont know how i can remove
> > those files and start again...
>
> Under normal operation the checkpoint process will look at the number of
> already created archive files, keep around up to (2*checkpoint_segments+1)
> of them for future use, and delete the rest of them. You never delete
> them yourself, the server will take care of that automatically once it
> gets to where it makes that decision. If you set checkpoint_segments to
> some very high number they can end up taking many GB worth of storage,
> increasing that parameter has at least two costs associated with it (the
> other being a longer recovery time).
>
i had commented archive_command in the main server but i only made a
reload. now i made a restart and almost all files in $DATA/pg_xlog/
dir are gone, and the server works properly :D
question: all the parameters in postgresql.conf need a restart to take effect?
thanks all for your help.
--
Roberto Scattini
___ _
))_) __ )L __
((__)(('(( ((_)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_xlog and standby
On Jan 23, 2008 2:28 PM, Erik Jones <[EMAIL PROTECTED]> wrote:
>
> You don't. The main server should not be keeping archived WAL files
> directly in pg_xlog/. As it queues WAL files to be archived it puts
> them in pg_xlog/archive_status/ with file names suffixed with .ready,
> once they are archived that suffix changes to .done after which, at
> some point (I'm not sure how long/many) they are removed.
>
, ok. the problem that im having is that i have A LOT of
archive files on pg_xlog dir, and thats because the archive_command
keeps failing (the standby server had filled his disk with archives
received but not proccesed), so now, i dont know how i can remove
those files and start again...
> Now, if you took your standby server offline, but didn't disable your
> archive_command then you've basically been accumulating WALs with
> the .ready prefix in the archive_status directory that, if you're
> going to start from scratch with your standby, you can safely
> delete. Just make sure you have a couple of WAL files successfully
> archived (suffix has changed to .done in the archive_status dir and
> you've verified that they've reached whatever directory your standby
> expects them to be in) before call pg_start_backup() and starting
> your new base backup.
>
> IMO, the most important point to be had here is DO NOT delete WALs
> that sit directly under pg_xlog/. Mistakes with the rest can be
> worked with, you could run into serious problems with your primary
> when deleting WALs directly under pg_xlog/.
>
yeah, i agree. but now i have aprox 40GB of archive files in pg_xlog
dir in the production server. :S
> Also, do you know why your standby stopped recovering? I'd say you
> should make sure you know why and how, otherwise you run the risk of
> the same thing happening again.
i dont know exactly, but it is very possible that it could be an
unfinished server re-config.
>
> Erik Jones
thanks for your help!
--
Roberto Scattini
___ _
))_) __ )L __
((__)(('(( ((_)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pg_xlog and standby
hello everybody:
im trying to reconfigure a warm-standby server. the problem is that
for some reason, one day the standby server stopped recovering the
archives. this leaded to a full disk on that server, so i turned off
(commented) the archive_command on the main server.
i want to restart the procedure described in
http://www.postgresql.org/docs/8.1/interactive/backup-online.html#BACKUP-PITR-RECOVERY
but i dont know how to "safely clean" the main server $DATA/pg_xlog/
dir.
with "safely clean" i mean how do i know which archives can i delete
(or move somewhere) without disrupting the normal operation of the
server.
im using postgres 8.2.5 from source on debian etch.
thanks in advance!
--
Roberto Scattini
___ _
))_) __ )L __
((__)(('(( ((_)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
