Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
3 votes
2 answers
798 views

Bug in PL/pgSQL function creation

I don't know if this question better suits here or in SO ... This is a script that I'd like to launch (the code of the function was copied from a question on SO): \c mydb create or replace function ...
4 votes
2 answers
836 views

MySql Vs. Postgresql for a blog application

I am designing a blog application. Which of these fits better? In which use cases will mysql be good and for what use cases will postgresql be good?
7 votes
2 answers
4k views

Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps. I had data loss few times and thanks to old backup i was always up and running. But for a recent project for Airport, ...
11 votes
2 answers
836 views

Biological Sequences of UniProt in PostgreSQL

What is the best way to store UniProt biological sequences in PostreSQL? Data Details We pull in 12 million sequences from UniProt - this number is likely to double every 3-10 month. The length of a ...
3 votes
1 answer
439 views

Multiple databases, in multiple geographic locations while preserving PostgreSQL triggers and data consistency

I've read a ton over 300 pages of db setups in the last day but I feel a little overwhelmed with all of the potential options and lack of documentation on how PostgreSQL triggers function across ...
3 votes
1 answer
3k views

PostgreSQL 9.1 vs MySQL 5.6 with InnoDB ? Which is better for scaling?

PostgreSQL 9.1 vs MySQL 5.6 with InnoDB. I do know that PostgreSQL offers better SQL features. I am currently using MySQL 5.6 lab release (the one with InnoDb full text search feature)(not in ...
2 votes
3 answers
2k views

Postgresql sync databases

I am having a postgres database on my portable and on a server. I would like to keep those two in sync as easy as possible. I know about slony but I am not able to figure out: Does it work when the ...
12 votes
1 answer
6k views

Constrain input to a few different strings

Hi I can't seem to get a constraint working the way I expect in postgreSQL. From within pgadmin I execute the following SQL query. -- Check: "TypeCheck" -- ALTER TABLE "ComLog" DROP CONSTRAINT "...
5 votes
2 answers
3k views

Pros and Cons of virtualisation technology for database-server and datastorage

Yesterday we had a chat about performance and restorability and I realized how many good things a virtualisation environment can bring to me - but as I'm a little sceptic concerning performance I'm ...
2 votes
1 answer
4k views

Postgresql revert back database value

I have postgresql database. My table name is tblvoippolicy. Now, problem is by mistake i have deleted some records from table. When I get the data file using following commands. select * from ...
5 votes
3 answers
1k views

Changing the port of the postgresql server for security reasons

For security reasons, it is common to change the port of Microsoft Sql Server. The subject is discussed here in Is changing the SQL Server port really that much safer? my question is: does this apply ...
8 votes
2 answers
7k views

How to force Windows to support not supported LC_COLLATE and LC_TYPE in PostgreSQL?

I have fresh installation of PostgreSQL 9.1.x. Unfortunately, I discovered that OS doesn't support LC_COLLATE = 'pl_PL.utf8' and LC_CTYPE = 'pl_PL.utf8'. I read in PostgreSQL documentation that: On ...
5 votes
2 answers
4k views

Recover PostgreSQL database from WAL errors on startup?

I'm trying to set up an OpenStreetMap server on an Ubuntu 12.04 machine using the Ubuntu packages listed at switch2osm.org. I initially installed and set up everything using a Northeast US-only map ...
2 votes
1 answer
239 views

How not to distribute reads in pgpool

I want to use pgpool to replicate one database to another. I want to do my processing on my master database (reads/writes/inserts/deletes), replicate that database to another database as it is ...
0 votes
1 answer
1k views

PL/pgSQL function + client-side lo_import

I have a problem with importing documents into PostgreSQL. I have a plpgsql function, which simplified could look like this: create function add_file(flag integer, sth varchar) returns void as begin ...
26 votes
1 answer
29k views

disable constraints before using pg_restore.exe

When I try to execute pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same: ERROR: insert or update on table "someTable" violates foreign key constraint "...
6 votes
1 answer
2k views

How to selectively replicate a PostgreSQL database

I would like to keep two PostgreSQL databases with the same schema, but have one replicated from a master that only keeps a small portion of the live data. Basically the master takes in live data ...
1 vote
1 answer
5k views

I would like to launch a pg_dump command from a remote computer

I would like to launch the command: pg_dump.exe -h 255.255.255.255 -p 5432 -U postgres -F p -a -v -f "test.db.dump" mydb from a remote computer. Both the database and the remote computer are under xp. ...
9 votes
2 answers
23k views

PostgreSQL crash recovery

We are planning to use PostgreSQL in our application but are concerned about crash-safety and recovery. I can't find any database crash recovery method or process in PostgreSQL. I know there must be ...
1 vote
1 answer
328 views

Slony not replicating changes from a dump file

I’m working on a project where we are using pg_dump to get the data out of one database and then using psql to load it into another. The second database is the master db in a Slony replication ...
4 votes
1 answer
961 views

Can you make SSL and SSL-only (hostssl) connections to PGPool?

The new version of PGPool (3.2) has an "SSL" section in the conf file, and directions on how to use it in the manual. However, pool_hba.conf specifically says that PGPool doesn't support SSL (i.e. ...
1 vote
1 answer
1k views

PostgreSQL hot standby promotion, multiple hot standbys

I'm trying to work out failover and disaster recovery procedures for a cluster of three servers. Streaming replication is being used with a high wal_keep_segments, no log shipping is happening. I need ...
10 votes
1 answer
15k views

Starting PostgreSQL server after a HDD crash results in FAILED STATE

I am using Fedora 15 with PostgreSQL 9.1.4. Fedora crashed recently after which: An attempt to start the PostgreSQL server : service postgresql-9.1 start gives Starting postgresql-9.1 (via systemctl):...
0 votes
2 answers
619 views

Transfer data from database1 to database2 using task queue

We have a SQL Server legacy database, db1, to put data of Project1. With change of certain tables(insert/update) in db1, we need to transfer the changes in another postgres database, db2 (used by ...
8 votes
1 answer
1k views

Concept of Schema in PostgreSQL

I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design. Why should I use it? Can it affect me in future if I decide to not think ...
1 vote
1 answer
475 views

Slony initial subscription is causing out-of-memory kernel panics. Why?

Context: We run Slony 2.0 with Postgres 8.4 on two CentOS 6 servers--one master, and one slave. Our database is about 30GB in size, which isn't unusual, but we do have a couple of tables that are ...
3 votes
2 answers
1k views

Display Postgres server output in terminal and record to logs at same time

I'm running Postgres 9.1 (Homebrew installation on Mac OSX) and I'd like to monitor my postgres server more closely. My question relates to logs. I'd like to get the logs displaying in a terminal ...
0 votes
1 answer
7k views

What is going wrong with postgresql initdb? Why is the `UTF-8` encoding not getting enforced?

I am using PostgreSQL 9.1. Trying to enforce UTF8 encoding as default. This is what I am doing. service postgresql initdb -E 'UTF-8' \ --lc-collate='en_US.UTF-8' \ --lc-...
5 votes
1 answer
1k views

How do you figure out what planner cost constants to use in Postgres?

I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I ...
6 votes
1 answer
374 views

I was interrupted while transferring a very large table. How can I pick it up from where it stopped?

I wrote a python script to select and insert rows between tables in different databases (from SQL Server to Postgres). the table has around 2000000000 rows and the transfer stopped somewhere in the ...
3 votes
1 answer
2k views

Tune postgres 9.1 to speedup deletes

I need to tune my postgres db to speed up delete queries. I cant't optimize my db structure yet, so I'm looking for options available on server level for postgres 9.1. I've seen in older posts the ...
2 votes
2 answers
324 views

Which option do I have to modify to see more records without press `q`

I'm connected remotly to my database server with cygwin in full screen. When I query my database with select id, name from table limit 27; I get 27 records and I can do a new query seeing the ...
14 votes
2 answers
5k views

Streaming replication and failover on PostgreSQL

I am doing a proof of concept on PostgreSQL replication. After the discussion on forum we decided to go with streaming replication as the performance is good compared to other solutions. PostgreSQL is ...
3 votes
1 answer
878 views

Pgpool-II: sequences advanced by 1 after PITR recovery

I have tested pgpool-II 3.0.1 and pgpool 3.0.4, PostgreSQL 8.4 In both cases I'm getting the same result: after online recovery on primary and target nodes all 'serial' values got "+1". Other nodes ...
2 votes
1 answer
489 views

pgpool connect failed: No such file /tmp.s.PGSQL.0 replication mode = true

Setup: I have two instances of Postgres 9.0.3 and pg_pool 3.14 all running on the same Ibuntu (10.04) machine and am attempting to evaluate pgpool. I can attach to pgpool using psql -p <pgpool ...
2 votes
1 answer
8k views

How to verify postgresql db restore from pg_dump file

How can I verify that my restored database is identical to my original database? Here's what I did. Is this a valid way to verify a restore? # dump mydb_orig: $ pg_dump --cluster 8.4/my.8.4....
5 votes
2 answers
3k views

Pipe psql timing to file

I am currently using \timing on to do some simple performance testing in Postgres. I would like to run many queries and pipe the timing results to a file. However, all of the options I have tried (\...
9 votes
3 answers
10k views

How to concatenate psql variables?

How can I concatenate two psql (PostgreSQL client) variables? I want to generate an absolute path by concatenating a directory path variable and a filename variable. I've tried this: \set path '/tmp/...
2 votes
1 answer
816 views

Is PostgreSQL 9.2 autovacuum sufficient for a busy table?

I have one table on a PostgreSQL 9.2/PostGIS 2.0.1 database: CREATE TABLE ch02.markers ( ff_id SERIAL PRIMARY KEY, col1 boolean, col2 smallint, created_at timestamp, lat double precision, ...
3 votes
1 answer
3k views

How to troubleshoot COPY FROM inserting less rows then expected?

I have a CSV (tab-delimited in fact) file with 7,590,051 lines, according to wc -l, which I want to import using COPY FROM. I removed the table's primary key, so it has no constraints. I ran COPY ...
5 votes
1 answer
3k views

Default value of serial fields changes after restore

Today I found a strange behavior after restoring a PostgreSQL database: the schema of all serial fields default values are trimmed out. For example: CREATE TABLE testschema.testtable ( id serial, ...
0 votes
1 answer
2k views

Drop and restore PostgresSQL database without losing settings

This seems like a simple, easy question, but I'm very new to PostgreSQL administration and I'm not sure of the best way to do things. I have a PostgreSQL (9.1) database that somehow got corrupted (...
6 votes
1 answer
6k views

PostgreSQL tree structure and recursive CTE optimization

I'm trying to represent a tree structure in PostgreSQL (8.4) to be able to query the path from the root to a given node or to find all the nodes within a sub-branch. Here is a test table: CREATE ...
0 votes
1 answer
361 views

postgresql dump and load -- need to dump "postgres" db also?

If I pg_dump a db from an 8.4 cluster, and then load it into a different 9.1 cluster, do I need to pg_dump and restore the 'postgres' db as well? (I'm not familiar with what is contained in the '...
0 votes
1 answer
524 views

Fill factor for later bigserial + index

I'm planning to import and update a huge table (3 Bil. rows) in the following step: create table with fill factor x import the data from csv files (6 files, ~21GB each) add a BIGSERIAL column create ...
1 vote
1 answer
2k views

PostgreSQL vacuum duration on a big table

I have a very big table in my postgreSQL (over 300 million rows) that has never been vacuumed. Yesterday I tried a vacuum and analyze (not a full one) and it took about 7 hours to complete. Will it ...
2 votes
2 answers
22k views

How to configure postgresql to use multiple cores? [duplicate]

How can I configure postgresql to use multiple cores in a single machine , is it automatically uses it ?
1 vote
1 answer
4k views

Two-way encryption in PostgreSQL version 9.1 database

What is the process of two-way encryption of data in PostgreSQL database version 9.1 using built-in functions?
3 votes
1 answer
4k views

How do I install pgcrypto on Windows?

I would like to install pgcrypto in Windows. I have a file C:\Program\PostgreSQL\9.0\share\contrib\pgcrypto.sql but when I try to run it in psql I get an error: C:: Permission denied How do I solve ...
4 votes
1 answer
658 views

PostrgeSQL 9.1 Certificate Authentication: how to get certificate common name of client?

I am using Certificate Authentication for clients on a PostgreSQL 9.1 database server. Several certificate common names are mapped to a single database user in pg_ident.conf. I do not want to create ...

1
310 311
312
313 314
317