Postgres Plus Forums - Latest forum topics

Trouble connecting on LB Port

ddossot — GMT

I can connect fine to each of my DB instances (on DB port) but having issues connecting on LB port.

I receive the following each time:

[code]psql: ERROR: "MD5" authentication with pgpool failed for user "..."[/code]

I've looked at "[i]Connecting to a Cloud Database Cluster[/i]" ([url]http://www.enterprisedb.com/docs/en/9.0/conncloud/Tutorial_Connecting_to_a_Cloud_Database_Cluster.htm[/url]) and the FAQs ([url]http://www.enterprisedb.com/products-services-training/products-overview/postgres-plus-cloud-database-faqs[/url])hoping for an answer but I can't find any.

What am I missing here? Am I supposed to configure pg_pool myself?

row is too big

aburmakov — GMT

I try to use EDB to move from Oracle to PostgresPlus AS and got error"row is too big: size 26068, maximum size 8164" when I am trying to load Oracle's package into DB.

SQL> @business_pkg.pkb
ERROR near line 1:
row is too big: size 26068, maximum size 8164

SQL> select version;

VERSION
--------------------------------------------------
EnterpriseDB 9.0.5.18 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 32-bit

> cat /etc/redhat-release
CentOS release 5.7 (Final)

> ls -l *.pkb
141217 business_pkg.pkb

What is a source of problem and how to solve it?

How to configure a Postgres Enterprise Manager Server on Postgres Plus Cloud Database

Gary_C — GMT

Vibhor Kumar has written a tutorial here: [url]https://vibhorkumar.wordpress.com/2012/02/06/configure-pem-server-on-postgres-plus-cloud/[/url]

SSPI - POSTGRES 9.1 64BITS- WINDOWS 7 64BITS -odbc -vb6

jesus_l — GMT

SSPI does not work, I get error
The multi-step operation generated errors OLEDB, check the status values OLEDB, if possible.
We did not perform any work

Any ideas, I'm testing on my development machine, install kerberos?, I must add something to the connection string?
thanks
regards

Oracle Compatibility - tables and columns in quotes

Timv256 — GMT

In Oracle the table "TABLENAME" is the same as TABLENAME without the quotes, so in some of our application select statements the table name is referenced with quotes and other cases without quotes because in Oracle, they are the same. So when I run a query on Postgres Advanced Server in Oracle Compatibility mode referencing TABLENAME, it is different than "TABLENAME". I am trying out Postgres Advanced Server to see if it will work for us and this is the major problem that I have encountered. Is there an easy way around this behavior? Is there a parameter to change it to work like Oracle? The same thing happens with column names.

Thanks in advance for any suggestions.

Error insert

hariszie — GMT

i have problem while insert data in the table.
i have function to copy data between 2 table, like table A to table B. but when i am run this function, data table B not match with table A.
example, i have record in table A with value 1.73 but value will change in table B to 2.00, why? i have used round function to get this record but is not work.

OpenSSL in StackBuilder

aaaaaaaaaa_aaaaaaa_3 — GMT

StackBuider does not provide OpenSSL.

While providing a lot of things not directly related to PostgreSQL (i think in web apps), StackBuider does not provide a core functionnality as OpenSSL.

After some diffculties in finding openssl for Windows, I installed it manually following PostgreSQL web site docs :
http://www.postgresql.org/docs/8.4/static/ssl-tcp.html

If someone could add OpenSSL in StackBuider it would be great.

create tablespace fails with a notice of truncation

Mark_Phillips — GMT

I am using a shell script to create a default database. I would like to create a tablespace in the "application support" directory on Mac OS X 10.7.

The script is as follows:

[code]PGDATADIR="$HOME/Library/Application Support/MyCompanyDIR/MyAppName/data"
mkdir "$PGDATADIR"
chown -R postgres:daemon "$PGDATADIR"
chmod -R 700 "$PGDATADIR"
psql postgres -U postgres << EOF
CREATE TABLESPACE "MyAppTS" OWNER master_user LOCATION '$PGDATADIR';
EOF[/code]

This results in an error message in log:
[quote]NOTICE: identifier "/Users/username/Library/Application Support/MyCompanyDIR/MyAppName/data" will be truncated to "/Users/username/Library/Application Support/MyCompanyDIR/MyAppNa"[/quote]

I have not encountered this before. What does it mean? How do I resolve it?

pgAgent Job Schedulling

sviyer — GMT

I am trying to schedule the backup process of one of my database on my local machine for demo purpose.I tried somewhat all the possible solution that i found on Internet but hardly any of them solved the problem might be there is something that I am missing,so kindly provide me with the guidance regarding the job scheduling on pg Admin using pg Agent also provide me with step to step guide if possible for all the stated problem.Also I like to mention that I want to do this thing with the help of a batch file which is working fine when executed directly from cmd.Also the pg Agent service working fine.

EC2 Security Group : Too many ports open to public

smpublisheradmin — GMT

I saw in EC2 security groups that too many ports are open for 0.0.0.0/0 . Following ports are understandable : [b]5432 [/b], [b]22[/b], [b]9999 [/b]. However the rest should be locked down.

In addition, it will be great if we can specify our own security groups. Basically , we want to restrict all public access to our database servers. In that case we can use security group for "Application Server" and add it to database server and instead of using public IP , we use public DNS (e.g : ec2-23-21-197-80.compute-1.amazonaws.com). Beauty of using this is

1. The public DNS resolves to private IP , if connected from EC2 instance.
2. With private IP , we can use security group for Application Server.
3. No data transfer cost if transferred using private IP in same region.

How to start migration to cloud?

kholet — GMT

Hi EDB Team,
Our team come up to the idea of trying the Postgres Plus Cloud Database, we want to move our existing edb and postgres databases from different cluster server going to cloud. The challenges after migrating all our databases what is the next thing to do, we have no idea how it can be possible. We need to know if we are the one who’ll create instances. Next, are we going to install edb again in our cloud server? Or we just need to migrate our databases with no configuration needed in cloud? Can we have clear explanation or step by step procedure? Hoping for your favorable response. Thank you.

Mac OS X Lion (10.7) -- What's the current status with the installer?

Basil_Bourque — GMT

What's the current status?
What issues should I know about?

----| Other discussions |----------

I've not seen any recent threads about using the One-Click installer for Postgres 9.1.2 on Mac OS X 10.7.2 (Lion).

I found this one recent thread:
http://forums.enterprisedb.com/posts/list/3030.page#11342
But it discussed memory allotments, but not Lion-specific issues.

There are older threads in these forums from last year, but they seem to deal with prior versions of Postgres or Lion. Likewise for the Google hits I've seen so far.

----| Version |----------

I see Apple is bundling Postgres 9.0.4 with Lion 10.7.2.

I'm talking about the regular client/desktop-edition of Lion, not the Server edition.

And I am focused on a "clean install" of Lion, not upgrading Snow Leopard where Postgres was already installed.

----| Users |----------

Apple seems to have created a Unix user and group both named "_postgres". In contrast, the One-Click Installer creates a user named "postgres" (without underscore).

One difference I noticed after using One-Click Installer is that when starting the Mac, the "postgres" user is now offered as another Mac user. But of course it is not a full-fledged Mac (GUI) user; trying to login at startup fails. So this user's appearance at startup is a non-issue, I assume.

----| Processes |----------

There seem to be no Postgres-related processes running by default, as seen in the "Activity Monitor" app while specifying "All Processes".

Does anybody know if the Apple-supplied Postgres is ever launched later, after startup? If so, at the very least a port-listening collision would occur. Why did Apple choose to bundle Postgres in the client/desktop edition? Is it used by any utilities?

----| Unknowns |----------

I tried the current installer on a throw-away installation of Lion. All seemed well. The pgAdmin app works, and I used it to create a new database.

I would like to install Postgres 9.1.2 on my real development Mac. So I am asking what issues or problems with Lion I should expect.

If there are any release notes for either the Installer or Postgres that discuss Lion issues, please refer me. I've not found any.

--Basil Bourque

Problems whith choosing the region

BigTorba — GMT

Hi.
I'm using Cloud Database-as-a-Service. But i have problem. All my instances situated at Ireland. When i create DB Cluster throught http://107.21.232.98:8080/ i can't choose Ireland region.
What should i do to create cloud database at ireland Region??

Selecting changed rows via xmin?

davesisk — GMT

I want to identify changed rows in a table(for an ETL feed), and pull a list every 5-15 minutes or so. I don't care whether the operation was insert or update (obviously, a deleted row won't be visible even if it hasn't been vacuumed yet). We can't change the schema of this system, so adding triggers is not really an option. Would it work reliably to do it via the xmin system column? Here's what I have in mind:

1) select max(xmin::text::bigint) from some_table;
2) store it as prior_xmin.
3) sleep for 5 mins
4) select * from some_table where xmin::text::bigint > prior_xmin;

I've tried this manually, and it seems to work well (except the queries aren't exactly fast since xmin isn't indexible...however, I can live with that...it's still quicker than selecting every row from the table). I don't care if I get a few too many rows (they will be MERGE'd/upserted at the target), so as long as I don't miss any changed rows I'm good. As I understand it, xmin will always increment up, until at some point it wraps around. (We'd probably just have to reload all rows into the DW at that point and start the "counter" over...however, I can live with that also...we're reloading EVERY row EVERY night right now, so almost anything is better, even if it isn't perfect.)

We're on 8.3/8.4. Is this a reliable approach, ignoring the eventual wrap-around caveat that I mentioned above?

TIA!
Dave

Silent installation on Windows

Alan_Cordner — GMT

Is it possible to do a silent installation on Windows using the one click installers? If so, can you point me to the documentation on this topic?

Postgres Plus Cloud DB Live Twitter Chat

paulina_lp — GMT

We're seeing a lot of good questions regarding the Postgres Plus Cloud Database on this forum, so I wanted to take the time to let you know that on Monday, January 30th at 10am PT, EVP of products and engineering Karen Padir will be answering your Postgres Plus Cloud Database Questions!

Join us and follow the chat using hashtag #CloudDB. We hope to see you there!

http://www.twitter.com/EnterpriseDB

EnterpriseDB Releases Postgres Plus Cloud Database for General Availability

Gary_C — GMT

For instructions on how to get started please visit: http://cloud.enterprisedb.com

Resizing a varchar column of large database table

atcr206 — GMT

I am looking to resize a column in an existing database table with > 120 million rows using the following approach ( {} are params ):

UPDATE pg_attribute SET atttypmod = {DesiredLength}+4
WHERE attrelid = {TableName}::regclass
AND attname = {ColName}

Does anyone see an issue with this approach? This would be much easier for me than creating a new column, copying data, deleting the old, and renaming the new.

Thanks in advance.

Outstanding Issues

gortonsm — GMT

Seems from reading the forum a number of questions/features remain [i]outstanding[/i] possibly preventing people using the Cloud product in a Production environment.

1. EBS: users want faster striped RAIDed EBS options under their LVM control (or build option).
2. Cache: offer existing Postgres cache (or AWS Elastic cache) from the go.
3. Encryption: beyond encryption in transit (SSL), securing the data at rest is important to some - please instruct how to.

In summary, great start and a [u]great initiative[/u] - more answers and options are maybe required before this moves seriously into Production.

Are you able to provide answers to the above?

EC2 version update, not really out of Beta?

ionearth — GMT

Very eager to use your product, but bugs have prevented us from using this in production.

You came out of Beta, but no new version for EC2? When can we expect the updated version for the EC2 version of the console?

Thanks,

Cant seem to be able to download postgresql installer

adviner — GMT

I cant seem to download postgresql windows x64. Every time i click on it i just get transferred to a different page. Ive tried it with chrome and ie and nothing
I selected version 9.1.2-1. Ive tried all of them and nothing.

http://www.enterprisedb.com/products-services-training/pgdownload

Thanks

Installer adds local-user on windows client while passing a domain-user

voik_e — GMT

Why does the one-click-installer (tried with postgresql-8.4.xx-windows.exe) create a local user (default postgres) even if the user exists in the windows domain?
I wanna install postgreSQL on Windows XP clients running the service under a domain-user with the one-click-installer in silent mode.
Parameters for the installer: --serviceaccount [domain]\[userid] --servicepassword defaultpw ... ans some others.
How can I prevent the creation of the local user? The only way i found is to delete the user after installation (net user xxx /del).
Is there a higher risk to run under domain-users?
Thanks for your answers,
Voik

Error creating plperl langauge with ppas-9.1

ajmcello78 — GMT

I am using Linux. I tried 9.0, and 9.1, get the following error:

createlang: language installation failed: ERROR: could not load library "/opt/PostgresPlus/9.1AS/lib/plperl.so": /opt/PostgresPlus/9.1AS/lib/plperl.so: undefined symbol: Perl_Iincgv_ptr

How do I fix? Can I build plperl for ppas-9.1.1.0 or is it binary only?

Thanks.

postgreSQL not available in stack builder 3.1.0

puma_1977 — GMT

Hello

I am new with postgreSQL and have managed to install postgreSQL 9.0.6.1 after several problems. No I want to proceed with installing PostGIS.

As I read there is the possibility to install it using the stack builder. I started the stack builder (3.1.0) but can't select any application on the first screen (I'd like to post a screenshot but don't know how to...). The only selection available is "remote server". Selecting this option leads me to different applications I can select and install but the spatial extensions and other extensions are missing. So no PostGIS is available.

Can you tell me what might be wrong? Is the installation of stack builder incomplete or is my installation of postgreSQL incorrect?

I am working with Windows XP, 32 bit,.

unattended 9.1.2 install fails on "unknown login: postgres"

Mark_Phillips — GMT

I am building an application installer for Mac OS X 10.7 that installs postgresql 9.x in the "unattended" mode. The main script copies the application files into place, then installs postgres via an invocation of installbuilder.sh. The main script is invoked with admin privileges. I invoke the pg installer in the script as follows:
[code]log "=== install DBMS engine ==="
./postgresql-9.1.2-1-osx.app/Contents/MacOS/installbuilder.sh --mode unattended --superaccount postgres --superpassword MySecretWord[/code]
On Mac OS X 10.7 "Lion", the pg installer runs along for a bit then fails out. The bitrock-installer.log contains the following statements:
[code]Preparing to Install
Executing su - postgres -c "\"/Library/PostgreSQL/9.1/bin/pg_ctl\" stop -m fast -w -D \"/Library/PostgreSQL/9.1/data\""
Script exit code: 1

Script output:

Script stderr:
su: unknown login: postgres [/code]
At this point, the system, osx 10.7, has a user account named "PostgreSQL" but not one named "postgres". There is, of course, the stock install of postgres that comes with Mac OS X 10.7 named "_postgres". I am trying to avoid confusion with that install and user.

After the error in the log are two more errors involved launchd then all subsequent scripts complete normally.

It appears the postgres group and user need to be added prior to installing postgres, but I wonder if that is really the issue. I have seen advice to modify the PostgreSQL user name, but that is after the installation has run, and that is not appropriate for an end user installer.

Is adding an account "postgres" the correct thing to do?

All tips, hints and links gratefully accepted.