On 3/4/2015 7:03 AM, María Griensu wrote:
I need to figure out how can I weight BLOB objects in a table of a DB,
I'm not expert on this topics, so I appreciate any help you can give me.
postgres's equivalent of BLOB is BYTEA. I'm not sure what you mean
by 'weight'
er to implement a HA cluster via
streaming replication, the master and slave each with their own
dedicated storage, and promoting the slave to master if/when the master
dies.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via
no clue how you're
inserting this date field.
test=# create table test (calendar date);
CREATE TABLE
test=# insert into test (calendar) values (null);
INSERT 0 1
test=# select calendar, calendar IS NULL from test;
calendar | ?column?
--+--
| t
(1 row)
--
j
On 3/6/2015 2:12 AM, Medhavi Mahansaria wrote:
I am porting my application from oracle to postgresql. in oracle it
enters as NULL
Oracle has the unique 'feature' that an empty string is NULL. This is
contrary to the SQL spec.
--
john r pierce
ive new data. in
our case its made fairly easy to track 'new' because all our OLTP
transactions are event-oriented.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general
On 3/8/2015 8:24 PM, Rob Sargent wrote:
I strongly suspect many would like further details on your implementation. A
user conference session well recorded perhaps?
the details are proprietary and quite specific to our workload and
requirements.
--
john r pierce
On 3/8/2015 10:32 PM, David G Johnston wrote:
This solves the explicit problem given the assumption that (Name, Total
Salary) is indeed a uniquely identifying constraint.
that constraint seems flawed to me.
--
john r pierce 37N 122W
somewhere on the
On 3/13/2015 6:51 AM, sameer malve ⎝⏠⏝⏠⎠ wrote:
Just use pgtune utility it will give an o/p of u r postgres.conf
depending on your machine hardware .
I find on newer large hardware, pg_tune makes some over-the-top choices.
--
john r pierce 37N 122W
lt in connection
pools), and configuring your app to grab a connection from the pool, do
a transaction and release the connection.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-ge
ere datefield < current_date-interval '6 months';
obviously, save the data you selected in a suitable archive file. repeat
this for each table you wish to 'archive and purge'.
alter the interval with whatever criteria you want to use for this
archive and purge operation.
code do that the existing
UTF8 support doesn't ?
--
john r pierce, from the mid left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 3/24/2015 5:16 AM, Мартынов Александр wrote:
There is postgres db with sepgsql enabled. When user connect to postgres db
with psql, postgres create new process for each connection. These processes
have selinux context unconfined_u:unconfined_r:postgresql_t.
Is there a way to assign the proc
On 3/24/2015 11:49 AM, Bankim Bhavsar wrote:
- 9.2.0
9.2 is currently at 9.2.10. 9.2.0 was released 2.5 years ago. several
of the bugs fixed in the 10 incremental updates were data corruption
related.
--
john, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-
On 3/24/2015 12:05 PM, Bankim Bhavsar wrote:
We'll upgrade to 9.2.10 and attempt to reproduce the issue.
If possible, can someone point to corruption related issues fixed
after 9.2.0?
in the postgres manual, see the release notes for 9.2.1 through 9.2.10
--
john, recycling bits in santa cr
On 3/24/2015 2:16 PM, Raymond O'Donnell wrote:
Is there a header row in the CSV file? - if so, delete it and try again.
or specify 'WITH HEADER' on the COPY command
--
john, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to
On 3/25/2015 2:19 AM, ginkgo36 wrote:
Hi all,
I have 1 table have:
- 417 columns
- 600.000 rows data
- 34 indexs
when i use query on this table, it so long. ex:
update master_items set
temp1 = '' where temp1 <> '' --Query returned successfully: 435214 rows
affected, 1016137 ms execution time.
On 3/29/2015 12:48 AM, Yuri Budilov wrote:
Red Hat/Oracle Linux 6.x
is that anything like Ford/Chevy ?
Oracle Linux, while originally forked from Red Hat Enterprise Linux aka
RHEL, has diverged significantly and is now its own thing. AFAIK, the
Postgres yum repository has made no effort at m
On 3/29/2015 1:39 AM, Yuri Budilov wrote:
my employer runs Oracle Linux 6.x and also Red Hat 6.x, so if we were
to drop Oracle database and take PostgreSQL instead, I am afraid,
everything must work on those two Linux platforms.
We pay for support from Oracle and Red Hat for Linux OS.
its ins
on it wouldn't work in Word.
--
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
ity of this really depends
on the use cases.
--
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
ertainly a 'very bad
thing' unless the transaction is actively crunching data.
if you're using a client such as JDBC which autowraps queries in
transactions by default, you need to invoke COMMIT's when you're done
doing that unit of work.
--
john r pierce, recycling bits in santa cruz
ad in connections, as one client connection can serve
multiple customers
--
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
mp -Fc, then you can specify the schema at pg_restore time.
--
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
sults, format them as you see fit for the email and
toss it at your language-of-choice's email facility.
--
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
am not sure where or
how the ldapsearchattibute comes into play.
you would do this by CREATE USER on the various servers for those
people, along with GRANT. LDAP only provides authentication, it doesn't
manage authorization.
--
john r pierce, recycling bits in santa cruz
--
Sen
he default port.
--
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
their LDAP profile.
but thats not how it works, so all the 'planing' in the world won't
change a thing.
access rights per database are managed with GRANT, users must be CREATE
USER on each server regardless of how they are authenticated.
--
john r pierce, recycling bits in santa cruz
exist before the
restore". External tablespaces directories are easy to create but what's about
pg_default and pg_global tablespace since I never created specific tablespaces for them?
those are created when you initdb the target cluster prior to restoring
the pg_dumpall output.
-
here not
datistemplate"); do \
pg_dump -Fc -f
/home2/backups/pgsql/pgdump.$i.$(date +\%a).dump $db
done
this creates a globals-only backup and a seperate backup of each
database, for each day of the week.
--
john r pierce, recycling bits
depends on your OS and/or the way things have been
installed.
you need to read the release notes to see if there's any special steps
due to bug fixes. some incremental updates may require a reindex of
certain index types, for instance.
--
john r pierce, recycling bits in santa cruz
/pgsql/pgdumpall.globals.`date +\%a`.sql.gz
for i in $(psql -tc "select datname from pg_database where not
datistemplate"); do \
pg_dump -Fc -f /home2/backups/pgsql/pgdump.$i.$(date +\%a).dump $i
done
--
john r pierce, recycling bits in santa cruz
about specific
employee types or groups, these other tables could have their own
primary key, but would reference the Employee table EmployeeID field for
the common employee attributes.
--
john r pierce, recycling bits in santa cruz
start_value3 and end_value3;
select * from table where (number * 3 between start_value1 and end_value2) OR
(number * 3 between start_value2 and end_value2) OR
(number * 3 between start_value3 and end_value3);
--
john r pierce, recycling bits in
On 5/29/2015 10:41 AM, John R Pierce wrote:
On 5/29/2015 9:32 AM, Arup Rakshit wrote:
Can I do the below 3 queries in a single query ?
select * from table where number * 3 between start_value1 and
end_value2;
select * from table where number * 3 between start_value2 and
end_value2;
select
a database specific ODBC driver,
which William already described.
now, if your code is making use of MySQL specific features, then its not
going to work with Postgres (but it also likely won't work with Oracle
or Access/Jet databases either).
--
john r pierce, recycling bits in santa
example, whats to prevent concurrent
transactions on distributed servers handing out the same money ?
--
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
the storage is exactly the same for those.
--
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
s for the data point :)
if all 5 alters' were to the same table, you should have combined them
into one alter statement.
--
john r pierce, recycling bits in santa cruz
. ask
the ssytem administrator to install...
yum install mpfr
if your host is not providing basic working development tools, you
either need to find a new host, or discuss this with management.
--
john r pierce, recycling bits in santa cruz
just to know if one of yours have seen this, or
something like this, before.*
use lsof or equivalent to determine what process owns the other side of
the socket that one of those processes is listening on. my guess is,
you're not releasing resources in django and they are piling up.
nd simpler,
unless you're using a programming framework that has native ODBC support
built in.
--
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
citly case sensitive according to the specifications.
--
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
? oh wait, you can't DO
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
erial, name text);
alter table info add primary key(id);
or more generically,
create index on some_table ( some_field[,...] ) ;
(a primary key is a unique not null constraint, this implies an index in
postgresql)
--
john r pierce, recycling bits in santa cruz
field
name. see
http://www.postgresql.org/docs/current/static/rowtypes.html#AEN7836
--
john r pierce, recycling bits in santa cruz
snapshotting (like zfs),
and the master is doing WAL archiving, you could, in theory, pause the
replication and snapshot the slave, do read/write tests on the slave,
then restore that snapshot and resume replication, pulling from the WAL
archive til it catches up.
--
john r pierce, recycling bits in
enerate_series(1, 300),
(generate_series(1, 300), 'hi')::info);
analyze test;
explain select * from test where getID(i) <http://i.id>= 1;
--
john r pierce, recycling bits in santa cruz
its woken back up after
being restored to how it was before your testing, it will query the
master, find out its way ahead of its timeline, and consult with the WAL
archives, fetching as many as are needed to catch up to the servers'
current timeline, then resume streaming ...
-
what is a standby meant
for fail over, and what is a clone meant for testing. With possibly
disastrous consequences.
VERY good point!
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription
highly suspect.
--
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
server for a few days from Amazon or someone.
--
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
you
merge those changes in ?
--
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
ansactional database processing.
--
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
e anything to abort..
you can't lock something thats already in use by a transaction as that
transaction already has locks on it.
better would be to design your workflow so it all can be done
transactionally and get away from this batch processing model.
--
john r pierce, recyclin
a password ? that way mucking with configuration
files is not required.
--
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
ugins and addons, etc.
at a bare minimum, a database administrator needs to create database
roles (users) and databases for an app like yours.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscri
On 7/6/2015 9:55 PM, c.bu...@posteo.jp wrote:
On 2015-07-05 22:16 John R Pierce wrote:
>at a bare minimum, a database administrator needs to create database
>roles (users) and databases for an app like yours.
The admin don't need to create the db. It is done by the application
ident' as your authentication, where your unix user is used as
the postgres username. or, you can use ssl certificates for
authentication, this is more complex to setup.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.or
ey when it runs.
how would that work for unattended scripts, such as cron jobs ?
--
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
Capture} approach.
Also, we used Ora2Pg tool to migrate the DB objects with some manual
syntax modifications.
thats the easy part.
now what about the massive code base of pl/sql and triggers he mentioned ?
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list
t in front of any local all
all lines ?
were you using localhost to connect or leaving the host empty? localhost
uses a host entry.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscrip
e index?
that suggests some folks overthink their indexing strategies and end up
'overoptimized'.
anyways, a simple REINDEX fixes all sorts of index fragmentation
--
john r pierce, recycling bits in santa cruz
un that psql command as the system
postgres user and not specified any -U or -d ...
--
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
se su (not sudo) from root,
or I'd cron it from the postgres DBA account, depending.
--
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
daemons as other users, they typically use su, like...
$SU -l postgres -c "$PGENGINE/postmaster -p '$PGPORT' -D
'$PGDATA' ${PGOPTS} &" >> "$PGLOG" 2>&1 <
/dev/null
(where $SU is su or runuser)
--
john r pierce, recycling bits
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
9.4 vs 8.4, not directly
related to the pg_upgrade process.
--
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
27;re not running as root.
--
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 8/1/2015 3:08 AM, Andreas Joseph Krogh wrote:
The version goes last:
service postgresql start 9.3
thats very OS/distribution specific.
--
john r pierce, recycling bits in santa cruz
performance most often, stripe all the
disks together and put everything on the same big volume, that way all
IO is evenly distributed. otherwise you'll find too much IO on some
tables/partitions, and not enoguh on others, so most of the disks are
idle most of the time.
--
john r p
on methods appropriately in pg_hba.conf... but before
messing with that, verify the server is actually running, or not.
Do note, /etc/init.d scripts are meant to be run by the root user only,
not by a regular user account.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgs
On 8/14/2015 11:06 AM, Ravi Krishna wrote:
is there any details available on this poll ?
the linked article links to this report, which requires registration to
download.
http://www.dbta.com/DBTA-Downloads/ResearchReports/The-Real-World-of-the-Database-Administrator-5237.aspx
--
john r
--
john r pierce, recycling bits in santa cruz
-fits-all answer. are you doing OLTP, characterized by
frequent small transactions with high percentage of update events and
many clients, or is this OLAP, where you bulk load large data, then do
relatively few very large aggregate type queries for reporting purposes ?
--
john r pierce, recy
ent.'
if you want clients to connect from other systems, then listen_addresses
= '*' is appropriate.
listen_addresses should have no impact on what your plpython app can
connect to outside of postgres, unless you're running the 'safe' version
of pl***
--
joh
enforce command,
and see if your python works.
--
john r pierce, recycling bits in santa cruz
if this is a reply to a list, I'm subscribed to that list, please reply to the
list not me personally.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes
n
the spot for incompetence ?
--
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
boot or restart anything, just do it)
--
john r pierce, recycling bits in santa cruz
tabase, then to let those apps mold a db into a non-optimum design.
if you let the app drive the database design, you tend to end up with a
database which is only useful to that single app, and likely breaks when
that app changes.
--
john r pierce, recycling bits in santa cruz
--
Sent vi
referencing your account numbers.
--
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
with a password
on the ssl key store.
But, how will that work with application programs connecting to
postgres, such as a webserver based application ?
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
ASE yourname OWNER yourname;
where yourname is your regular login.now exit psql, exit the su, and
try running pgadmin. don't specify localhost, leave the host
empty/blank, and you should be allowed to log in as yourname.
explaining why this is just as it is requires more energy than I ha
7; or PITR, which will let you restore the database to just
before any specified transaction number, so if bad data got introduced,
you can restore it to just prior to that point.
you might read all of this chapter in the manual...
http://www.postgresql.org/docs/9.4/static/backup.html
--
joh
-3-7.html
http://www.postgresql.org/docs/current/static/release-9-3-8.html
http://www.postgresql.org/docs/current/static/release-9-3-9.html
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
iew" role that you want to access the
"preview" schema, then the default search path would suffice, as $USER
== preview.
--
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 9/21/2015 5:46 AM, Kjetil Nygård wrote:
2. yum repo for Fedora 23
Is it possible to have fedora 23-repo for PostgreSQL? Especially 9.4
and 9.5.
I thought F23 starts beta next week?
it appears 9.4.4 is included in the F23 base repository.
postgres 9.5 is itself still in beta.
--
john r
perl function.
--
john r pierce, recycling bits in santa cruz
sion 9.4.0 Devrim Gunduz
# Remove PGPORT variable
so... how do you run multiple versions??
--
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 9/29/2015 10:09 AM, John R Pierce wrote:
does the 9.4 yum install no longer support using the sysconfig
settings fragments ? I see what looks like the correct shell code for
it in /etc/rc.d/init.d/postgresql-9.4 ...
# Override defaults from /etc/sysconfig/pgsql if file is present
to 8k.
--
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
ize.
zfs set recordsize=8192 zpool/pgdata
--
john r pierce, recycling bits in santa cruz
lot going on
there abstracting things.
--
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
/postgresql/9.3/main
The directory . should be owned by the postgres user, and it should have
700, 750, or 770 permissions. all the files in it should also be owned
by postgres.
also look and see if postgres logged anything in its own system log
files (/var/log/postgresql/9.3 or whatev
ng
it out there.
--
john r pierce, recycling bits in santa cruz
On 10/6/2015 11:24 AM, droberts wrote:
OR a dimension 'type' with values outbound/inbound and a single measure
column 'total' ?
that smells a bit too much like an "EAV" (entity-attribute-value) which
is considered an antipattern in relational circles
--
jo
to me what needs to be done do recover.
postgresql-9.3 9.3.0-2.pgdg12.4+1
what method or software were you using to perform this backup?
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to
;ve done.
actually, there've been numerous cases where NoSQL deployments never
worked right, and people reverted to SQL for reporting.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
um install postgresql93
...
(note, the last command just installs the runtime libraries and
utilities, it does NOT install the postgresql 9.3 database server, that
is package postgresql93-server)
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgs
Visualizer.
I don't know of any ERD tool that will recognize foreign key references
without FK constraint definitions, regardless of what things are named.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
database so what threat does a stronger hash address?
if you need stronger security, don't use passwords, use ssl
certificates, or LDAP, or something.
--
john r pierce, recycling bits in santa cruz
1 - 100 of 2337 matches
Mail list logo
