Re: [GENERAL] Multi tenancy : schema vs databases
>do you run a separate instance of the app for each tenant, or is there one app >that identifies the >tenant and handles them accordingly ? Each tenant will have different app server. there will be persistent connection for each tenant. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multi tenancy : schema vs databases
On Sat, Oct 1, 2016 at 4:52 PM, John R Pierce wrote: > On 10/1/2016 12:52 PM, Rakesh Kumar wrote: > > Do your clients authenticate directly to the database, or to the app server? > > thru app server. > > > do you run a separate instance of the app for each tenant, or is there one > app that identifies the tenant and handles them accordingly ? > > > -- > john r pierce, recycling bits in santa cruz > > *I would like to comment on the multiple schema vs databases situation. First of all, 1000's of databases is insanity and just asking for trouble. Next, 1000's of schemas is a nightmare to maintain. I understand the requirement for client data to be "isolated", but in reality, data is never really separated. Once it's on the server, any good hacker with a knowledge of SQL can find it. So, IMHO, the best solution is to isolate by a client ID in the tables of one database. Then make sure you have sufficient and correct security on those tables.* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Multi tenancy : schema vs databases
On 10/1/2016 12:52 PM, Rakesh Kumar wrote: Do your clients authenticate directly to the database, or to the app server? thru app server. do you run a separate instance of the app for each tenant, or is there one app that identifies the tenant and handles them accordingly ? -- john r pierce, recycling bits in santa cruz
Re: [GENERAL] Multi tenancy : schema vs databases
On 10/1/2016 11:39 AM, Jeff Janes wrote: As others have said, different databases makes connection pooling less efficient, which could be very important to you, or could be irrelevant. 1000 apps running at once each with their own DB and 10s of connections == 10s of 1000s of database connections, which means 10s of 1000s of database processes. 1000 apps only connecting as needed for each transaction == database server forks for every connection, expensive. -- 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
Re: [GENERAL] Multi tenancy : schema vs databases
>Are you restoring because your whole system failed, or because one client did >something >wrong and needs just their data rolled back? Chances of restoring just for one client will probably be 99% of use cases. > Do your clients authenticate directly to the database, or to the app server? thru app server. thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multi tenancy : schema vs databases
On Thu, Sep 29, 2016 at 12:18 PM, Rakesh Kumar wrote: > > Hi > > I would like to know which technique is better for supporting > multi-tenancy= > applications, going upto hundreds or even thousands of tenants. > > 1 - One database with difference schemas (one schema per tenant) > or > 2 - One database per tenant. > > The points to be considered are: > > 1 - which is more light weight from resources point of view. > Multiple databases used to be more resource intensive, but that was mostly fixed in 9.3. Now I don't believe there is all that much difference while running (databases are much more heavy to create in the first place). As others have said, different databases makes connection pooling less efficient, which could be very important to you, or could be irrelevant. > 2 - which is easier for backup/restore > That depends on how you want to backup restore. If you use log archiving, it will make no difference. If you use sql dumps, then do you want to make it easier or harder to backup and restore all clients together, or all of them separately? Are you restoring because your whole system failed, or because one client did something wrong and needs just their data rolled back? > 3 - Which is better from security p.o.v > Any implementation can be done wrong in a way that causes security problems. Do your clients authenticate directly to the database, or to the app server? Cheers, Jeff
Re: [GENERAL] postgres failed to start from services manager on windows 2008 r2
PHANIKUMAR G schrieb am 01.10.2016 um 17:30: We then tried to start the service with the following changes. a. we changed the "Log on as" type to "This account" b. changed the account name to ".\" c. And entered the password for this account and saved. After that we are able to start the postgresql service successfully. Found that the pg_log directory is created under data directory with postgres-Sep.log Why it is failing with local system account, the local system account is part of administrators group. If we specifically provide credentials to the service as explained above, service getting started. Please help me to understand what is causing. Where is the data directory? And how did you create the data directory? Check the privileges on the data directory. This sounds as if the local system account does not have the privilege to write to the data directory. The local system account (or "Administrator") don't have the privilege to read and write all files. Those accounts only have the ability to give themselves these privileges. Also: if I'm not mistaken, Postgres 9.3 creates the service with the "Local Network Service", not with "Local System Account" So how did you create the initial service? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres failed to start from services manager on windows 2008 r2
Hi, Please help me on this issue, postgresql is failing to start from Windows services manager. Below is the scenario we performed. Verified services manager that postgresql service was registered and failed to start. We tried to start the service manually and observed the following. a. fail to start the postgresql service. b. found that pg_log directory is not created under data directory. c. found that "log on as" type as "Local System account" We then tried to start the service with the following changes. a. we changed the "Log on as" type to "This account" b. changed the account name to ".\" c. And entered the password for this account and saved. After that we are able to start the postgresql service successfully. Found that the pg_log directory is created under data directory with postgres-Sep.log Why it is failing with local system account, the local system account is part of administrators group. If we specifically provide credentials to the service as explained above, service getting started. Please help me to understand what is causing. Best regards Phani Kumar On Sep 26, 2016 12:31 PM, "PHANIKUMAR G" wrote: > hi, > >We are able to start the postgres from command line without any issue, > if postgres is registered as windows service and tried to start it, we are > facing an issue . > > *Problem:* > > We have registered postgres(version 9.3.4) as windows service on windows > 2008 R2 and registration got succeeded. > > we are performing below 2 commands to register postgres as windows service > > *pg_ctl.exe register -N "prostgresService" -D > "fullpath_to_data_directory" -W* > > *>sc description prostgresService "prostgresService Applicatio* > *n Server Database Service"* > > when we try to start the service from services manager(services.msc) it is > failing and popping up below message > > *"The prostgres service on Local Computer started and then stopped. Some > services stop automatically if they are not in use by other services or > programs "* > > postgres does not even create log and its directory pg_log in pgsql\data > directory. > > windows event log has a message > > *The description for Event ID 0 from source PostgreSQL cannot be found. > Either the component that raises this event is not installed on your local > computer or the installation is corrupted. You can install or repair the > component on the local computer.* > > *If the event originated on another computer, the display information had > to be saved with the event.* > > *The following information was included with the event: * > > *Timed out waiting for server startup* > > > could you please help us how to start, we are clueless since postgres is > not creating log except windows event log. > > > thanks in advance > Phani Kumar >
