Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
0
votes
1answer
54 views

How to relate to information_schema

Is it possible to create a foreign key that relates to information_schema? For an application implementation, I would like to store extra column information in another table, but I want to ensure ...
0
votes
1answer
778 views

Can PostgreSQL index calculations be multithreaded?

During a single query write of approximately 300,000 rows with 30 columns all indexed, I noticed that only one core was maxed at a time. Is there any way to calculate indexes multithreaded for single ...
0
votes
0answers
4k views

huge size of /var/lib/postgresql/9.3/main/base/1 directory (postgresql)

i'm using postgresql 9.3.4 and real size of database is about 5.5 GB but POSTGRESQL fill disk about 256 GB !! is this POSTGRESQL Cache and what is method to clean thing issue ?
0
votes
0answers
97 views

Why is PostgreSQL doing a seq scan on column != column?

Why is PostgreSQL doing a table scan on column != column? Isn't this query guaranteed to return no results? Isn't anything != anything guaranteed to be always false for any record on a not null ...
0
votes
1answer
213 views

Keeping bank transactions in sync

I'm counting on your expertise in this one, I'm developing a scrapper for banking transactions history. My problem is, scrapping should be done regularly and the bank doesn't supply any unique ...
0
votes
0answers
136 views

Slow search times with functions despite index?

I'm aware that this question might be under the guidelines of being closed, but I'm out of ideas at the moment on how to optemize this table any further. Given a have a few billion records with ...
0
votes
1answer
855 views

How to get pg_archivecleanup on Amazon Linux 2014.03?

I have an Amazon EC2 instance based on Amazon Linux AMI 2014.03. I installed PostresSQL from their repos: yum install postgresql-libs postgresql postgresql-server postgresql-devel I've read about ...
0
votes
1answer
86 views

Postgresql Function Trying to Insert into 1 row instead of 2

Postgresql 9.3, Mac OS X 10.6.7 I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows. First, here is the Table T1: ...
0
votes
0answers
452 views

PostgreSQL and Single Table Inheritance

I read about STI (Single Table Inheritance) and like the idea. Does PostgreSQL handle 95% NULL values in some columns if I have about 1M rows well? Does it impact performance if there many columns (~...
0
votes
0answers
159 views

PostgreSQL Failover strategy

I'm lost when it comes to choose the failover technology for my postgresql database. The options I currently see are: PG Cluster Streaming Replication Warm Standby (log shipping?) Hot Standby Is ...
0
votes
0answers
1k views

COPY TO .csv and add quotes to header in PostgreSQL

I use the following in the command line \copy (select id, pid from table1) to '/path/to/file.csv' with delimiter ';' CSV HEADER; Everything works fine. But I want the header's string to be quoted in ...
0
votes
1answer
94 views

Postgresql database backup (and restore) from two servers to third storage place

I have two server machines with PostgreSQL: (A) is version 9.0.15 and (B) version 9.2. I wish to make backups of the databases stored in (A) and (B) - and be able to do restore if needed too! - to a '...
0
votes
0answers
46 views

How can I figure out what service is trying to connect as root?

In my postgresql log I see this from time to time: 2014-05-20 12:34:03 CEST FATAL: role "root" does not exist I would not expect any of the services that I have configured to connect as root. How ...
0
votes
0answers
2k views

how to append or update JSON datatype value in postgreSQL

hey guys i'm using PHP's codeigniter to create my application and postgreSQL as database and i have to update some value in database. So, let's say we have table structure like this:- CREATE TABLE ...
0
votes
0answers
1k views

Error while taking backup in Postgresql

While taking a backup from my PostgrSQL database, it is showing this: pg_dump: Dumping the contents of table "gtab17" failed: PQgetResult() failed. pg_dump: Error message from server: ERROR: invalid ...
0
votes
0answers
735 views

Mirror database on same server instance for better read performance?

I am developing a somewhat big web application, which uses data which is aggregated using a crawler. The crawler is constantly writing to the database and updates many tables at the same time. For the ...
0
votes
0answers
281 views

Can I speed up the psqlodbc driver?

I have a SQL Server Integration Services 2012 package that copies data from SQL Server 2012 to Postgres 9.3. I'm using the latest version of the psqlodbc driver (9.3.210) but I only can transfer ...
0
votes
0answers
91 views

Adding additional condition significantly degrades performance

Have the following query: SELECT * FROM table_A INNER JOIN table_B ON /* conditions */ INNER JOIN table_C ON /* conditions */ INNER JOIN table_D ON /* conditions */ WHERE table_B.state = 'active' ...
0
votes
0answers
128 views

Update query taking 8x times in hot standby environment of Postgresql

I configured streaming replication (hot standby) and tested the update query on it. The update query is taking 8 times more when compared with stand-alone PostgreSQL (9.2 version) system. Some ...
0
votes
0answers
148 views

Postgresql not working: error

My Postgres was working fine on my ubuntu 12.10 before i installed postgres-xc and execute a command initdb for changing encoding. After that it started showing error like: psql: could not ...
0
votes
0answers
65 views

Organizing a couple tables in postgresql

I have a data structure that is represented like this: { 'question_id': 1, 'is_required': False, 'question_text': [ { 'id': 1, '...
0
votes
0answers
79 views

Reduce read write delays in specific database setup

I have a database with a large table with 15 columns (type double). It contains a large amount of data (~ 100 rows of data for every second. Holds 3 days of data). I also have a real-time program that ...
0
votes
0answers
2k views

database filesystem backup error

I get this error earch time: postgres=# select pg_stop_backup(); NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be archived WARNING: pg_stop_backup still waiting for all ...
0
votes
0answers
17 views

In Amazon Redshift, running GRANT as a superuser does not grant privileges on table to a regular user [duplicate]

I'm trying to run a query in Amazon Redshift, but I keep getting the error permission denied for relation mytable As far as I understand, this is a privileges issue. According to this question: ...
0
votes
0answers
107 views

How to config Enterprisedb work with PostgresXC ?

Util now, I created a cluster (PostgreSQL database + Postgres-XC) with 5 server: 1 GTM, 1 Coordinator, 3 Data Nodes. I intend to create a Enterprisedb (Postgres Plus Advanced Server, link: EDB's home ...
0
votes
0answers
235 views

What size of hardware/VM for database with over 1,000,000 posts, 20GB?

I am running a Rails application with a Postgresql database. I am exited to say that I am scaling rapidly, and I now have over 1,000,000 posts, not including the User table, in Postgresql(approx 22GB)....
0
votes
0answers
1k views

Performance measurement in PostgreSQL

When developing server-side stored procedures performance measurement is an important issue. EXPLAIN ANALYZE is very usefull, but when it comes to simple queries like SELECT name FROM clients WHERE ......
0
votes
2answers
460 views

PgPool fails to auth in master_slave mode

We have two PostgreSQL servers running in master-slave streaming replication mode, behind pgpool. When we activate master_slave mode in pgpool.conf, we get following error: do_md5 failed in slot 1 ...
0
votes
0answers
138 views

How large should a database table grow?

I have been experiencing some very slow queries for a specific table. This table grows daily. I ran the pg_relation_size function, which gives the size of a table in the database: mydb=# select ...
0
votes
0answers
684 views

requested shared memory size overflows size_t PostgreSQL

I am running PostgreSQL 9.2.3 on SUN Solaris 9. This is 64 bit system with 32G swap and 16G RAM. I use same configuration file as on Linux or SUN Solaris 10, where everything is ok. I am unable to ...
0
votes
2answers
2k views

25M x 25M inner join (postgresql) performance

I have a one time need to do an inner join of 25M rows on 25M rows. The box is a Alienware area 51, 4 cores 25GB memory and SATA drive (non system disk). So far it has taken 22 hours. I did btree ...
0
votes
0answers
501 views

Postgres vs MySQL Database Maintenance

I have been using Postgres and MySQL for a long time now. There are a few routine maintenance chores that must be performed on a regular basis to keep a PostgreSQL server running smoothly. As as ...
0
votes
0answers
75 views

postgres database program to save sql templates and search where used it

I have a postgres database with tables and functions build using sql templates. And I would like to use a database program to save these templates and search where I used it. What i want to do is to ...
0
votes
0answers
2k views

PostgreSQL Install Error

When I install the server for the first time, when almost finished it reports: (from log file "%temp%\bitlock_installer1376.txt") Loading additional SQL modules... Executing cscript //NoLogo "C:\...
0
votes
0answers
1k views

PostGres- How to loop in Table to update every record in Table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update the ...
0
votes
0answers
771 views

Is there failover server parameter in PostgreSQL connection string?

Is there failover server parameter in PostgreSQL connection string that can be used to automatically switch from primary server to secondary sever ? We have been using similar functionality on MS SQL ...
0
votes
0answers
1k views

Can’t connect to local PostgresSQL server

I have the Postres app installed an running on my Mac. And it has worked beautifully. I don’t know what changed, but now I cannot connect to it from Rails, PG Commander, PG Admin or the command psql -...
0
votes
0answers
1k views

Can't connect to local Postgres databases

After trying to install Postgres using Postgres.app and EnterpriseDB, I decided to re-install it using Homebrew (As I got some problems connecting to database). PYTHON=/usr/local/bin/python brew ...
0
votes
0answers
1k views

What size of data can PostgreSQL handle?

We wanted to use OpenERP along with PostgreSQL database. OpenERP has a functionality to upload documents and stored in to database in binary format. Our worry is if user decides to upload to huge ...
0
votes
2answers
2k views

Postgres 9.2 - Create View Different Column Names

I want to create a view of a table with a column renamed. I do not want to effect the source table, just the view. I need to rename the column addr:housename to addr_housename. We have a 3rd party ...
0
votes
0answers
488 views

Postgres chooses a bad query plan

The following query finishes in 1.5s (which is ok, the table contains about 500M rows): explain (analyze, buffers) select sales.* from sales join product on (product.id = sales.productid) join date ...
0
votes
0answers
98 views

set up proper role/group acces on databases and schemes

On my postgresql server I'm struggeling / finding the best practice for a proper role/group setup. I have a number of clients on one machine; each client has its own role: client1; client2;client3 ...
0
votes
1answer
161 views

Nullable One To Many w/Join Table - Indexing

I have a question regarding database design, I am working with PostgreSQL using Hibernate for ORM, the design I inherited includes the following tables users user_id (pk) user_name user_dob ... ...
0
votes
0answers
1k views

PostgreSQL Cascade Delete not working

What am I missing here? I have two tables: CREATE TABLE source ( "sourceid" serial, "state" character varying(255) REFERENCES states (state), "leadtime" character varying(255), CONSTRAINT ...
0
votes
0answers
137 views

Postgresql function taking long time on newly restored database

I have developed one function in my PostgreSQL database. It was taking 12-15 seconds to execute. Now I have restored a new PostgreSQL 9.0 dump using following command: pg_restore -hx.x.x.x -p5432 -d ...
0
votes
0answers
285 views

Streaming Replication Resource Management (PGSQL) on FreeBSD 8.x

I am attempting to set up PGSQL-HA streaming replication on freeBSD 8.2 (willing to upgrade to 8.4 if need be). The set up is simple: 2 nodes, one active one standby. I have successfully set up ...
0
votes
0answers
57 views

Postgres 9.1.6 Error: index contains unexpected zero page at block 0 [duplicate]

I have setup streaming replication on Postgres 9.1.6 running on a debian server and it's going on fine. When I try to run a query on the replica DB I get the error below: ERROR: index "...
0
votes
0answers
57 views

Calculating the row size of a table [duplicate]

Is there a way to calculate the row size (in bytes) of a particular table in postgresql / pgadmin3?
0
votes
0answers
4k views

Cannot run pgAdmin from the terminal: No such file libwx_gtk2u

(Probably irrelevant) I'm using Puppet to setup/manage a class of computers that are equipped with PostgreSQL and PostGIS, and this implies that each computer goes through an unattended install: ...
0
votes
0answers
1k views

Postgresql Retrieve / Find Primary Key Serial Autonumber field for Audit logs

I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs. All the primary keys are type serial (auto incrementing integer) and all have the name in the ...