Java program. As
Adrian said, in this case, you configure sql workbench/j to use it,
rather than installing it in your OS like regular programs.
--
john r pierce, recycling bits in santa cruz
/products-services-training/pgdownload#windows ?
the version numbers you give above don't match any I see there. 8.4
isn't even supported anymore, hasn't been for a long time.
--
john r pierce, recycling bits in santa cruz
suggestion?
thank yo
what operating system are you running?
--
john r pierce, recycling bits in santa cruz
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
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 pi
, it would be smart to have
multiple resilient network paths
--
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
On 7/10/2016 4:28 PM, Patrick B wrote:
archive_command = 'cp %p /var/lib/pgsql/archive/%f'
That would be ok right guys?
normally, you want to ship your WAL archives to a NFS server or
something similar, which the master and all the slaves can read.
--
john r pierce, recycling bits
version, via
replication slots.
or via an external WAL archive, that the master writes, and the slave
has access to.
--
john r pierce, recycling bits in santa cruz
software you use,
postgres won't failover on its own.
--
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
to cluster app
servers, its not so easy to parallelize database servers.
--
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
versions at once (necessary for major version upgrades).
--
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
On 7/7/2016 10:36 AM, Melvin Davidson wrote:
It would help if you provided the version of PostgreSQL and the O/S.
we still don't know what OS you're using here, and if its something like
linux, what desktop environment you're using (gnome, kde, mate, etc) ?
--
john r pierce, recycling
ssh sessions, too, using ssh
clients like putty or securecrt.
--
john r pierce, recycling bits in santa cruz
parts between the OS desktop clipboard, and psql.
how about using psql -f file_of_commands.sql?
--
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
to any cascaded slaves,
repeat.
copying WAL archives around via rsync just seems cray-cray to me.
[*] in the latest version, there are 'replication slots' which provides
a completely different mechanism for this catch-up that doesn't require
WAL archiving
--
john r pierce, recycling bits
at all.
if your master is keeping a wal_archive, slaves, including cascaded
streaming slaves, can all use that same archive as their wal source,
these are only used for catchup when streaming is interrupted.
--
john r pierce, recycling bits in santa cruz
On 7/6/2016 6:30 PM, Patrick B wrote:
Someone from another list told me that wouldn't be possible on the 9.2
version, that's why I asked even that I read that doc...
it was new in 9.2, 9.1 didn't support cascading.
--
john r pierce, recycling bits in santa cruz
-REPLICATION
--
john r pierce, recycling bits in santa cruz
e, you need both sets of code. install 9.4 (from the
PGDG yum repository) separately. the PGDG 9.4 code should go into
/usr/pgsql/9.4 and it will by default look for its data in
/var/lib/pgsql/9.4/data (so move your data directory to that...)
--
john r pierce, recycling bits in santa cruz
. this goes much
faster if both 'data' directories are on the same file system, and you
use the 'links' option...
--
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
.
--
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
be of help to me.
select name from t group by name having count(id)>3
will return all names with more than 3 records in a single query... now
the question is, what do you want to do with this information ?
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mail
.
--
john r pierce, recycling bits in santa cruz
On 6/20/2016 8:03 AM, Scott Mead wrote:
I believe that free space is only available to UPDATE, not INSERT.
incorrect. in fact, an update is performed identically to an INSERT +
DELETE(old)
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql
wanted in terms of the motherboard/cpu/ram.
I would, however, also buy a LSI/Avago SAS 9207-8i card and remove that
3ware 9750 'hardware' raid controller, which you can probably get $200
for on ebay.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql
On 6/15/2016 9:39 PM, Shaun Cutts wrote:
Is there a reason besides “its bad design” to disallow tables with many columns?
it would require significantly different internal architecture optimized
for said huge rows.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql
the
source database and inserting into the destination.
--
john r pierce, recycling bits in santa cruz
ent reference manual,
https://www.postgresql.org/docs/current/static/index.html
--
john r pierce, recycling bits in santa cruz
there's a whole lot of implied magic here unless you want to get way
more specific what these features do, exactly, under all possible
conditions.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
. the message queue
paradigm ('publish/subscribe') is a very powerful way of implementing
complex distributed systems.
--
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
On 6/8/2016 7:04 PM, Patrick B wrote:
FOR crtRow in EXECUTE 'select DISTINCT(account_id) from
backup_table WHERE migrated = 1 AND account_id IN '|| $1
where and account_id in 21;
? I don't think that's what you want.
--
john r pierce, recycling bits in santa cruz
On 6/8/2016 6:47 PM, Patrick B wrote:
21 is the number of IDS that I wanna perform that COPY command
that didn't answer my question. if you call your function like SELECT
myfunction(21); as you showed, where are those 21 ID's coming from?
--
john r pierce, recycling bits in santa
On 6/8/2016 5:46 PM, Patrick B wrote:
Single id as you show, a range of numbers or an array of numbers?
select function(21);
Where 21 = Number of ids
how do you get the specific ID's from "21" ?
--
john r pierce, recycling bits in santa cruz
want to return from this function...
--
john r pierce, recycling bits in santa cruz
to recover the data, if you're lucky and careful.
that won't work if he did a DROP DATABASE as all the metadata is gone.
--
john r pierce, recycling bits in santa cruz
On 6/2/2016 4:42 PM, David G. Johnston wrote:
ssh user@hostname ?
^ ++
--
john r pierce, recycling bits in santa cruz
from yourtable);
do note, this is whats known as an 'anti-join', and these can be pretty
expensive on large tables.
--
john r pierce, recycling bits in santa cruz
s only one... hosts can be multihomed,
postgres can be listening on numerous interfaces, there is no 'the IP'
--
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/mail
to do.
--
john r pierce, recycling bits in santa cruz
, it will require copying the whole file with the new/changed
record in place.
I would recommend instead importing your XML/CSV/etc data into SQL
table(s), do your data operations there, then export the results back as
XML or whatever if you really need it that way.
--
john r pierce, recycling bits
, and set the log
prefix to include timestamping, then you could scan those logs to get
that information.its not otherwise stored in the database.
--
john r pierce, recycling bits in santa cruz
isualbasic macros
for data manipulation.
--
john r pierce, recycling bits in santa cruz
by state;
--
john r pierce, recycling bits in santa cruz
based on VisualBasic.
--
john r pierce, recycling bits in santa cruz
showed max 16, average 5 idle
connections. thats a rather small number to be concerned with. if it
was 100s, then I'd be worrying about it.
--
john r pierce, recycling bits in santa cruz
, and a
network socket. its not using CPU or disk IO.
--
john r pierce, recycling bits in santa cruz
On 5/15/2016 10:23 PM, Gavin Flower wrote:
4. recreate indexes for tableA
note on a large table, this step can take a LONG time. its greatly
facilitated by setting maintenance_work_mem = 1G beforehand.
--
john r pierce, recycling bits in santa cruz
elect the desired
authentication method for that combination.
--
john r pierce, recycling bits in santa cruz
I would like it to connect with user x but drop to password
authentication.
'ident' is only secure over local 'domain' sockets, not over tcp/ip.
that said, you can use an ident user map to do what you want, this would
say '"nobody" can log on as A, B, or C'
--
john r pierce, recy
looks like a really complicated query with lots of joins
and sorts etc etc..
--
john r pierce, recycling bits in santa cruz
On 5/4/2016 9:56 PM, drum.lu...@gmail.com wrote:
If I comment the line: *ELSEIF NEW.code IS NULL THEN*, the data is
inserted into the users.code column.
in the table definition, whats the default value of 'code' ?
--
john r pierce, recycling bits in santa cruz
your pg_hba.conf file.
--
john r pierce, recycling bits in santa cruz
uot; from acct_v9
where stamp_inserted >= '2016-04-26' and stamp_inserted <=
'2016-04-30' and tag=246 group by ip_dst order by "RX Bytes" desc
limit 10
union
select 'Total' as "Reciever", sum(bytes) as "RX Bytes" from acct_v9
where stamp_in
bytes gets 'toasted'
and stored out of line in special 'toast' tables.
--
john r pierce, recycling bits in santa cruz
, then I'll
symlink /var/lib/pgsql/9.4 to /u01/pgsql/9.4 or whatever.
--
john r pierce, recycling bits in santa cruz
to see if its a
keyword, but if not, revert it to its original case.
Why? PostgreSQL is written in C. So use strncasecmp() instead of
strncmp() or strcasecmp() instead of strcmp() to test for a token.
are those the APIs the parser uses?
--
john r pierce, recycling bits in santa cruz
code). otherwise the parser would have to
lower() every token to check to see if its a keyword, but if not, revert
it to its original case.
--
john r pierce, recycling bits in santa cruz
of other data corruption problems, 9.0.13 fixed yet more GiST
index problems. of course, every one of these incremental updates
fixed dozens of relatively obscure bugs, you'd need to read the release
notes for each version between 9.0.8 and 9.0.23 for the complete list.
--
john r
ous
postgres consultancies, like 2nd Quadrant, and so forth. I sure don't
know the intricacies of doing this.8.3 is a really old obsolete
version, too... its life cycle ran from Feb. 2008 to Feb 2013.
--
john r pierce, recycling bits in santa cruz
9.1.21, 9.2.16, 9.3.12, 9.4.7, and 9.5.2
--
john r pierce, recycling bits in santa cruz
. that blog is
talking about postgres 8.4, based on his paths. is that what you're
running?
I'd be hesitant to rely on random blog advise, without thoroughly
understanding what you're doing.
--
john r pierce, recycling bits in santa cruz
'state' or 'status' or something
that shows if the query is waiting, locked, or running. pg_stat_activity
has one row for each open connection.
--
john r pierce, recycling bits in santa cruz
is generated. Then
the worker needs to wake up and do its stuff.
Maybe this can be done in a different way than listening for
notifications?
that could be done with a trigger.
--
john r pierce, recycling bits in santa cruz
key... doesn't that
violate one of the fundamental tenets of the relational model ?
--
john r pierce, recycling bits in santa cruz
limitation of one query using only one core. But
is there any limitation of one CPU running only one query at a time?
no such limitation. any chance there's locking involved here? take a
look at pg_locks and pg_stat_activity the next time you run such a test.
--
john r pierce, recycling bits
On 4/22/2016 8:36 AM, Yury Zhuravlev wrote:
I'm working on incremental backup based on tracking of memory pages
(ptrack).
Now project in beta stage. And I did not have many feedbacks for catch
bugs.
doesn't a wal archive give you pretty much the same thing?
--
john r pierce, recycling bits
for things like triggers and such but not
for direct client queries.
--
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
job on things like joins, aggregates,
etc, your ANDL is going to be completely reinventing the wheel, its own
query optimizer, figuring out how to use indexes, all the postgres
datatypes and things like postGIS, etcetcetc ?
huh.
--
john r pierce, recycling bits in santa cruz
--
Sent via
) in
that column.
Of course that has to be unique, as nobody can use the same value of
others.
thats really hard to do correctly under a concurrent workload
- I was hoping you cans could help me to start doing the function...
--
john r pierce, recycling bits in santa cruz
pretty sketchy.
--
john r pierce, recycling bits in santa cruz
are applied on a whole block basis.
so is that one undetected error per 10^-14 blocks read, or what?
and what about file systems like ZFS that already do their own CRC ?
--
john r pierce, recycling bits in santa cruz
? thats
not 'silent'.
whats the rate of uncorrectable AND undetected read errors ?
--
john r pierce, recycling bits in santa cruz
other thread here
the other day.
who runs/owns uservoice, what are they in this for?Who setup this
postgresql uservoice site?
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscr
didn't see that in your list of tuning parameters.I
generally set it to 1GB.
--
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
to a function.
the transaction has already been started before your function is called.
and you can not issue a COMMIT from anywhere but the top level. you CAN
have savepoints, which act something like nested transactions within a
function.
--
john r pierce, recycling bits in santa cruz
of failure, although a proper
enterprise SAN has a rather high reliability (5-9's is not uncommon for
EMC enterprise class hardware) as long as its all properly designed
(redundant switches, dual HBAs in each server, etc)
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql
th letters were substituted with
other letters that gave more significance to it. Over time, fu was
somehow misheard as foo, and coders commonly started using it in
examples. Not Cool!*
omg, grow up.
--
john r pierce, recycling bits in santa cruz
On 4/13/2016 9:12 PM, drum.lu...@gmail.com wrote:
I know.. but unfortunately the bosses don't want to spend money :(
Time Actually Is Money.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
7.0.0.1/32 and ::1/128 to that.
--
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
On 4/13/2016 1:52 PM, John R Pierce wrote:
... will speed that up is faster disks
I left out faster RPM disks... faster sequential transfer speeds are
generally of little impact to write-bound database servers as most of
the writes are random, so its an issue of IOPS rather than MB
read or write IO. many of our database servers are
nearly 100% write IO, only thing that will speed that up is faster disks
and/or more disks in raid10.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes
that it consumes?
to free the disk space, use...
vacuum full tablename;
I don't know how to reclaim tuples that were written but rolled back.
--
john r pierce, recycling bits in santa cruz
server (or at least seperate postgres
instance aka cluster), and use pg_basebackup to rebuild this test instance.
--
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.postgresq
up/restore) - better if in
one operation (something like "copy database A to B")?
I would like to run this everyday, overnight, with minimal impact to
prepare a test environment based on production data.
copy to the same machine, or copy to a different test server? different
answers.
On 4/8/2016 6:16 AM, Marllius wrote:
thank you, but i need a link in official postgresql documentation
as postgresql is operating system independent, its pretty unlikely there
will be discussions of specific file systems in the official documentation.
--
john r pierce, recycling bits
On 4/9/2016 1:30 AM, Durumdara wrote:
In MS we had...
If you want Microsoft's unique version of SQL, run Microsoft SQL. That
stuff you describe is a whole bunch of implementation specific wierdness
from the standpoint of someone outside, looking in..
--
john r pierce, recycling bits
, but, to each their own.
and my experience is that in RHEL 6 and 7, XFS works very well, and IS
my preference for data volumes.
--
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
On 4/7/2016 3:21 AM, Berend Tober wrote:
John R Pierce wrote:
On 4/6/2016 3:55 AM, Alexey Bashtanov wrote:
I am searching for a proper database schema version management system.
At my $job we're old school. our schemas are versioned. there's a
settings table with (setting TEXT, value
On 4/6/2016 11:31 PM, sgringer wrote:
I have found it in this folder /usr/lib/postgresql/9.5/bin
the command "pg_rewind" don't work globally like othe pg
$ export PATH=/usr/lib/postgresql/9.5/bin:$PATH
$ pg_rewind --help
--
john r pierce, recycling bits in santa cruz
--
Sent
.
we're quite careful about how we modify our schema so it can be done
online, update the schema on the live database, then update and restart
the application/middleware.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
worth. a
spike at 330mmeans one per 3 points. the coarse grid units on that
graph were weeks, I don't know exactly what the fine data points were,
but not more more than a few per day.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql
you don't specify
a different template.
its also possible some management software might use it as a default
place to connect so they can get a list of databases or whatever .
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.or
to diagnose and
repair BDR problems. I have no experience running BDR, so will step
out of this thread (please delete me from CC's).
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription
On 3/30/2016 2:52 PM, Francisco Reyes wrote:
On 03/30/2016 05:44 PM, John R Pierce wrote:
and what if commit db2 fails for any number of reasons? you've
already committed db1, so you can't roll it back. this sort of
work requires '2pc' (2-phase commit), which is rather gnarly
fails for any number of reasons? you've already
committed db1, so you can't roll it back. this sort of work
requires '2pc' (2-phase commit), which is rather gnarly to implement.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general
On 3/29/2016 11:40 PM, Deole, Pushkar (Pushkar) wrote:
WITH t AS (
SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5
)
SELECT * FROM t ORDER BY record_date DESC;
why do it twice when you can just do
select * from t order by record_date desc limit 5;
--
john r
s all in-memory
--
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
l the available
'close_wait' states in your OS.
you might try a connection pool, such as pgbouncer. pgbouncer would
open some fixed number of database connections, perhaps 50 or so, and
your clients would connect to pgbounce to get a connection from the
pool, use it, then release it.
-
at pg_largeobject
I would as soon use a NFS file store for larger files like images,
audio, videos, or whatever. use SQL for the relational metadata.
just sayin'
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
suggestion, they don;t want to make any app changes
so they want someone else to make major architectural changes. great.
--
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
301 - 400 of 2328 matches
Mail list logo