Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (5)

Resolved PostgreSQL advice

  1. Advice
0
Votes
Undo
  1. Steen Jakobsen
    Steen Jakobsen
  2. PowerBuilder
  3. Friday, 12 January 2024 04:54 AM UTC

Hi my fellow PowerBuilder enthusiast :-)

Can you advise me if PostgreSQL is a good and fast and stable database. Currently using Firebird 2.5 (2.5 is an old version that is really hard to upgrade) and it is unstable sometimes and not fast when database grows above 2 GB.

So is it worth the investment to switch to PostgreSQL do you think ?

 Our software is based on on PB 2022R3 and is compiled to 64 bit

Thank you so much in advance

 

//Steen

 

ps. we are using SQL Server for high-end installations.

 

  1. Database
  2. PostgreSQL
  3. PowerBuilder (Appeon)
IIS configuration with pb 2021
PB 2019 R3 to 2022 R3 Migration -Discontinued Bui...
Responses (5)
mike S
mike S Accepted Answer Pending Moderation
  1. Friday, 12 January 2024 05:14 AM UTC
  2. PowerBuilder
  3. # 1

I assume you considered the express (free) version of sql server as well since you are already "using SQL Server for high-end installations."?  the main issue is the 10GB  database limit.  We use express our budget client/server implementations.

I've used Postgresql some in the past, but not in a production environment.  I had started a conversion to Postgresql back in PB 11/12 timeframe but hit some limitations in pb (which have been resolved by appeon).  At that time, I had most of the features of application running using it and it ran fine.   

FWIW - Microsoft has postgresql as an option on their azure database as a service.  so they see it as a very solid option:  Pricing - Azure Database for PostgreSQL Single Server | Microsoft Azure   I plan at some point testing this out for our powerserver database as it seems cheaper than azure sql server.

 

 

 

Comment
Load more comments
mike S
  1. mike S
  2. Saturday, 13 January 2024 16:18 PM UTC
you can also backup to azure which is pretty cheap. you could make it a service for your customers and charge a little bit
  1. Helpful 1
mike S
  1. mike S
  2. Saturday, 13 January 2024 16:25 PM UTC
the backup scripts are just the sql server backup commands: some examples:

https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver16



You can backup to a file (and then have that backup to dropbox etc) or backup to azure blob services directly. Or tell your customers to backup the backup themselves.



to run it from command prompt: use sqlcmd https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver16&tabs=go%2Cwindows&pivots=cs1-bash



or maybe build it into your application.



  1. Helpful 1
Steen Jakobsen
  1. Steen Jakobsen
  2. Monday, 22 January 2024 06:44 AM UTC
Thanks Mike
  1. Helpful
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Friday, 12 January 2024 05:23 AM UTC
  2. PowerBuilder
  3. # 2

Well Steen,

After going through the PostgreSQL forum, I got clarified that PG does support a good-sized DB.

I here below give you one of the mails, that that community received.   He is talking about a 50 TB Database.  

***************************************************

Hi

 
I am trying to use pgBackRest for all my Postgres servers. I have tested it on a sample database and it works fine. But my concern is for some of the bigger DB clusters, the largest one being 50TB and growing by about 200-300GB a day. 
 
I plan to mount NAS storage on my DB server to store my backup. The server with 50 TB data is using DELL Storage underneath to store this data and has 36 18-core CPUs.
 
As I understand, pgBackRest recommends having 2 full backups and then having incremental or differential backups as per requirement. Does anyone have any reference numbers on how much time a backup for such a DB would usually take, just for reference. If I take a full backup every Sunday and then incremental backups for the rest of the week, I believe the incremental backups should not be a problem, but the full backup every Sunday might not finish in time. 
 
I think converting a diff/incr backup to a full backup has been discussed here, but not yet implemented. If there is a workaround, please let me know. Or if someone is simply using pgBackRest for a bigger DB (comparable to 50TB), please share your experience with the exact numbers and config/schedule of backups. I know the easiest way would be to use it myself and find out, but since it is a PROD DB, I wanted to get some ideas before starting.
 
Thanks
Abhishek
*******************************************************
HTH
 
Happiness Always
BKR Sivaprakash
 
Comment
Steen Jakobsen
  1. Steen Jakobsen
  2. Friday, 12 January 2024 06:10 AM UTC
Thanks BKR Sivaprakash for fetching this document. So size is NOT an issue.
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Friday, 12 January 2024 07:38 AM UTC
We are using PostgreSQL, in place of SQL Server (Express), for the past 2 years. With the same server and slightly increased no. of clients, clients have not complained about the performance so far. Current PG DB size is around 12 GB.

  1. Helpful 3
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Friday, 12 January 2024 07:46 AM UTC
Would like to add that the PG Community generally recommend Linux for PG installations.
  1. Helpful 2
There are no comments made yet.
Dan Cooperstock
Dan Cooperstock Accepted Answer Pending Moderation
  1. Friday, 19 January 2024 18:25 PM UTC
  2. PowerBuilder
  3. # 3

We're using Firebird 2.5 for the apps we sell, and don't find it unstable at all. BUT .. because of the particular vertical market we are in, most of our clients' DBs would be no more than 25MB or so. 

Very occasionally something goes wrong with the indexing in a client's DB, but that can be fixed by GFIX. If that doesn't fix it, a backup followed by restore definitely fixes it.

Comment
Load more comments
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Wednesday, 24 January 2024 06:34 AM UTC
Armeen,

Our customers are not sure of getting internet connection everywhere they plan to use. For doctors, they won't get internet connection at few places (eg. govt. hospitals, remote villages).



Thanks Dan for the info of embedded version. When googled I could read about that but could get the link from where to download that version. Any help in that area. I might give another try, if I have issues in using SQLite.
  1. Helpful
Dan Cooperstock
  1. Dan Cooperstock
  2. Wednesday, 24 January 2024 15:29 PM UTC
Embedded Firebird 2.5.9 is on page https://firebirdsql.org/en/firebird-2-5/. That's what we're using for our standalone installs. Using the server version for non-standalone installs.
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Thursday, 25 January 2024 11:56 AM UTC
Thanks Dan.

My bad, I just skipped the 32-bit Embedded in between.. It's clear there, don't know how I skipped. Thanks once again.
  1. Helpful
There are no comments made yet.
Steen Jakobsen
Steen Jakobsen Accepted Answer Pending Moderation
  1. Monday, 22 January 2024 06:46 AM UTC
  2. PowerBuilder
  3. # 4

Well when the db grows you are in for trouble ;-)

Comment
There are no comments made yet.
Alex Kirpichny
Alex Kirpichny Accepted Answer Pending Moderation
  1. Tuesday, 16 April 2024 12:15 PM UTC
  2. PowerBuilder
  3. # 5

it all depends on what problems you have or expect with the Firebird database. in general, Postgres has advantages, but whether you need them, whether you will really use them, depends on your task. in addition, primarily for security reasons, it is highly recommended to use the current version (of any database) and update it constantly.

investments in switching will also depend on the number of embedded SQL in the application. if there is a lot of it and it is dynamic, then converting and debugging it may be more difficult than migrating the database itself.

if you decide to migrate a Firebird to a PostgreSQL or another database, I recommend trying this software. it transfers not only tables and data, but stored procedures, triggers, views and functions as well.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.