473,721 Members | 2,073 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

One Database per Data File?

I'm a complete newbie to postgres so please look the
other way if these questions are really stupid.

Is it legitimate to have one database per data file? For
organizational and backup purposes, I'd like to keep the
database files for each of several projects separate.
This means, e.g., that postmaster must have multiple
instances going simultaneously?

I'm thinking the answer is NO because, for one, the TCPIP
connection seems to be to ONE instance of postmaster which
then sorts out which database objects are in its container.

Am I close?
--
% Randy Yates % "Maybe one day I'll feel her cold embrace,
%% Fuquay-Varina, NC % and kiss her interface,
%%% 919-577-9882 % til then, I'll leave her alone."
%%%% <ya***@ieee.org > % 'Yours Truly, 2095', *Time*, ELO
http://home.earthlink.net/~yatescr
Nov 23 '05 #1
7 2876
Oops! Randy Yates <ya***@ieee.org > was seen spray-painting on a wall:
I'm a complete newbie to postgres so please look the other way if
these questions are really stupid.

Is it legitimate to have one database per data file? For
organizational and backup purposes, I'd like to keep the database
files for each of several projects separate. This means, e.g., that
postmaster must have multiple instances going simultaneously?

I'm thinking the answer is NO because, for one, the TCPIP connection
seems to be to ONE instance of postmaster which then sorts out which
database objects are in its container.

Am I close?


Not terribly.

For a given "cluster" (e.g. - an instance initialized using "initdb"),
you have a set of databases, each of which is indicated by a directory
under 'base/' in that cluster.

Within each database in the cluster, each table and index is indicated
by one (or more, if size > 1GB) files.

Thus, each database will have numerous data files, essentially one per
table and one per index.

If you rummage around in the files, you can learn quite a lot about
the structuring of things. Each file has a number; that number
corresponds to the OID number in pg_class.

Thus, if you find a file called "17441," then you could find out more
about it by the query

select * from pg_class where oid = 17441;
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc") )
http://cbbrowne.com/info/internet.html
"I love the way Microsoft follows standards. In much the same manner
that fish follow migrating caribou." -- Sinister Midget
Nov 23 '05 #2
Christopher Browne <cb******@acm.o rg> writes:
Oops! Randy Yates <ya***@ieee.org > was seen spray-painting on a wall:
I'm a complete newbie to postgres so please look the other way if
these questions are really stupid.

Is it legitimate to have one database per data file? For
organizational and backup purposes, I'd like to keep the database
files for each of several projects separate. This means, e.g., that
postmaster must have multiple instances going simultaneously?

I'm thinking the answer is NO because, for one, the TCPIP connection
seems to be to ONE instance of postmaster which then sorts out which
database objects are in its container.

Am I close?
Not terribly.

For a given "cluster" (e.g. - an instance initialized using "initdb"),
you have a set of databases, each of which is indicated by a directory
under 'base/' in that cluster.


That does not seem to be the case. I have three subdirectories in
my base/ directory, but according to PGADMIN III, only one database.
Within each database in the cluster, each table and index is indicated
by one (or more, if size > 1GB) files.

Thus, each database will have numerous data files, essentially one per
table and one per index.

If you rummage around in the files, you can learn quite a lot about
the structuring of things. Each file has a number; that number
corresponds to the OID number in pg_class.

Thus, if you find a file called "17441," then you could find out more
about it by the query

select * from pg_class where oid = 17441;
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc") )
http://cbbrowne.com/info/internet.html
"I love the way Microsoft follows standards. In much the same manner
that fish follow migrating caribou." -- Sinister Midget


Ahh, ok. So how does this answer my question or help me achieve
my goal of one database per "initdb file set?" You also have not
answered whether or not postmaster can have multiple instances
running, each pointing to a different initdb file set.
--
% Randy Yates % "How's life on earth?
%% Fuquay-Varina, NC % ... What is it worth?"
%%% 919-577-9882 % 'Mission (A World Record)',
%%%% <ya***@ieee.org > % *A New World Record*, ELO
http://home.earthlink.net/~yatescr
Nov 23 '05 #3
After takin a swig o' Arrakan spice grog, Randy Yates <ya***@ieee.org > belched out:
Christopher Browne <cb******@acm.o rg> writes:
Oops! Randy Yates <ya***@ieee.org > was seen spray-painting on a wall:
I'm a complete newbie to postgres so please look the other way if
these questions are really stupid.

Is it legitimate to have one database per data file? For
organizational and backup purposes, I'd like to keep the database
files for each of several projects separate. This means, e.g., that
postmaster must have multiple instances going simultaneously?

I'm thinking the answer is NO because, for one, the TCPIP connection
seems to be to ONE instance of postmaster which then sorts out which
database objects are in its container.

Am I close?


Not terribly.

For a given "cluster" (e.g. - an instance initialized using "initdb"),
you have a set of databases, each of which is indicated by a directory
under 'base/' in that cluster.


That does not seem to be the case. I have three subdirectories in
my base/ directory, but according to PGADMIN III, only one database.


Look more closely; there _are_ three databases there. If PGAdmin III
is saying otherwise, it's hiding something.

I don't know what the "one" is, but the other two are definitely
template0 and template 1. template1 is used as the "default template"
for new databases that are created; template0 is locked down against
updates.
Within each database in the cluster, each table and index is indicated
by one (or more, if size > 1GB) files.

Thus, each database will have numerous data files, essentially one per
table and one per index.

If you rummage around in the files, you can learn quite a lot about
the structuring of things. Each file has a number; that number
corresponds to the OID number in pg_class.

Thus, if you find a file called "17441," then you could find out more
about it by the query

select * from pg_class where oid = 17441;


Ahh, ok. So how does this answer my question or help me achieve my
goal of one database per "initdb file set?" You also have not
answered whether or not postmaster can have multiple instances
running, each pointing to a different initdb file set.


The "goal of 1 db per initdb" isn't strictly possible, because there
will always be template0 and template1. But I suppose you could
configure things to forbid access to anything other than the one
database that you _want_ used.

As for the 'multiple instances' part, that may be possible.

I have hosts on which there are four instances of PostgreSQL running.
Four instances of initdb; four init scripts; four base "postmaster s;"
four different ports for clients to connect to.
--
output = ("cbbrowne" "@" "cbbrowne.c om")
http://www3.sympatico.ca/cbbrowne/advocacy.html
debugging, v:
Removing the needles from the haystack.
Nov 23 '05 #4
Christopher Browne wrote:
That does not seem to be the case. I have three subdirectories in
my base/ directory, but according to PGADMIN III, only one database.

Look more closely; there _are_ three databases there. If PGAdmin III
is saying otherwise, it's hiding something.


Check out Display -> "System objects" from the menu.

Martin

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 23 '05 #5
Christopher Browne <cb******@acm.o rg> writes:
After takin a swig o' Arrakan spice grog, Randy Yates <ya***@ieee.org > belched out:
Christopher Browne <cb******@acm.o rg> writes:
Oops! Randy Yates <ya***@ieee.org > was seen spray-painting on a wall:
I'm a complete newbie to postgres so please look the other way if
these questions are really stupid.

Is it legitimate to have one database per data file? For
organizational and backup purposes, I'd like to keep the database
files for each of several projects separate. This means, e.g., that
postmaster must have multiple instances going simultaneously?

I'm thinking the answer is NO because, for one, the TCPIP connection
seems to be to ONE instance of postmaster which then sorts out which
database objects are in its container.

Am I close?

Not terribly.

For a given "cluster" (e.g. - an instance initialized using "initdb"),
you have a set of databases, each of which is indicated by a directory
under 'base/' in that cluster.
That does not seem to be the case. I have three subdirectories in
my base/ directory, but according to PGADMIN III, only one database.


Look more closely;


There was nothing wrong with my eyes.
there _are_ three databases there. If PGAdmin III
is saying otherwise, it's hiding something.
As Martin kindly pointed out, PGADMIN III hides these other two
databases (the one being my application database which I created).
I don't know what the "one" is, but the other two are definitely
template0 and template 1. template1 is used as the "default template"
for new databases that are created; template0 is locked down against
updates.


Right, with system objects unhidden, that is the case reported by
PGADMIN III.
Within each database in the cluster, each table and index is indicated
by one (or more, if size > 1GB) files.

Thus, each database will have numerous data files, essentially one per
table and one per index.

If you rummage around in the files, you can learn quite a lot about
the structuring of things. Each file has a number; that number
corresponds to the OID number in pg_class.

Thus, if you find a file called "17441," then you could find out more
about it by the query

select * from pg_class where oid = 17441;


Ahh, ok. So how does this answer my question or help me achieve my
goal of one database per "initdb file set?" You also have not
answered whether or not postmaster can have multiple instances
running, each pointing to a different initdb file set.


The "goal of 1 db per initdb" isn't strictly possible, because there
will always be template0 and template1. But I suppose you could
configure things to forbid access to anything other than the one
database that you _want_ used.

As for the 'multiple instances' part, that may be possible.

I have hosts on which there are four instances of PostgreSQL running.
Four instances of initdb; four init scripts; four base "postmaster s;"
four different ports for clients to connect to.


Ah - good idea! Map the individual databases to specific ports. Thanks!
--
% Randy Yates % "Bird, on the wing,
%% Fuquay-Varina, NC % goes floating by
%%% 919-577-9882 % but there's a teardrop in his eye..."
%%%% <ya***@ieee.org > % 'One Summer Dream', *Face The Music*, ELO
http://home.earthlink.net/~yatescr
Nov 23 '05 #6
Not even close. PostgreSQL uses one or more files per
table/index/sequence/etc. Each database has its own directory. I think
with tablespaces you can even spread a database over multiple
directories.

Secondly, every connection gets its very own postmaster, they can can
each access any file they wish.

Hope this helps,

On Mon, Sep 06, 2004 at 02:47:56AM +0000, Randy Yates wrote:
I'm a complete newbie to postgres so please look the
other way if these questions are really stupid.

Is it legitimate to have one database per data file? For
organizational and backup purposes, I'd like to keep the
database files for each of several projects separate.
This means, e.g., that postmaster must have multiple
instances going simultaneously?

I'm thinking the answer is NO because, for one, the TCPIP
connection seems to be to ONE instance of postmaster which
then sorts out which database objects are in its container.

Am I close?
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBQoouY5T wig3Ge+YRAo3HAJ 9JRx5FYnYox6KSO y878NnV2EizOwCf aaiR
2waD67KYY0vI4HL eifbFmCo=
=8UQu
-----END PGP SIGNATURE-----

Nov 23 '05 #7
Randy Yates <ya***@ieee.org > writes:
Ahh, ok. So how does this answer my question or help me achieve
my goal of one database per "initdb file set?" You also have not
answered whether or not postmaster can have multiple instances
running, each pointing to a different initdb file set.


You definitely can. Each one needs a different value of PDGATA and
PGPORT. You also need to make sure shared memory limits, semaphores
etc are beefy enough to handle all the instances.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3939
by: Fran Tirimo | last post by:
I am developing a small website using ASP scripts to format data retrieved from an Access database. It will run on a Windows 2003 server supporting FrontPage extensions 2002 hosted by the company 1&1 with only limited server configuration via a web based control panel. My query relates to the ASP security model and how it relates to FrontPage options for setting file access on a database file. If you know of any online documentation...
6
7341
by: Marvin Libson | last post by:
Hi All: I am running DB2 UDB V7.2 with FP11. Platform is Windows 2000. I have created a java UDF and trigger. When I update my database I get the following error: SQL1224N A database agent could not be started to service a request, or was terminated as a result of a database system shutdown or a force command.
10
6041
by: MHenry | last post by:
Hi, We were going merrily along for 6 years using this database to record all client checks that came into our office, including information about what the checks were for. Suddenly, network computers cannot access the database. The message is...
5
6575
by: Seok Bee | last post by:
Dear Experts, I currently trying to use the FileUpload control from asp.net 2.0 to upload files. The uploading of the file I would like to store it in the Access Database. Unfortunately, I've no idea how I can do that. Can anyone provide me some solution by writing the code in vb.net? Many thanks in advance. Regards,
9
3831
by: Wayne Smith | last post by:
I've come up against a major headache that I can't seem to find a solution for but I'm sure there must be a workaround and I would really be grateful of any help. I'm currently building a web site for a small club I belong to and one of the features I would like to include is the ability to allow users to upload image files. unfortunately the servers web root www folder only allows READ and EXECUTE permissions, which makes it...
18
9143
by: surfrat_ | last post by:
Hi, I am having the following problems in getting Microsoft Visual Studio 2005 Professional to link to an Access .mdb database. Please help me to sort this out. Problem 1: The Microsoft page "How to: Connect to Data in an Access Database"
10
13405
by: eholz1 | last post by:
Hello Members, I am setting up a photo website. I have decided to use PHP and MySQL. I can load jpeg files into the table (medium blob, or even longtext) and get the image(s) to display without a problem. I am using chunk_split(data) and the base64_encode and base64_decode on the files. I do a select from the database, and then echo the image (with header(Content Type: image/jpeg) and the decoded image displays fine. Yes, I have...
2
5418
by: Scott Jones | last post by:
Hello, In an attempt to save disk space, I went in and gzipped all of the log files for my database running DB2 version 7.2 . This resulted in the database being marked bad because a log file was no longer found (I think). Here is an excerpt from db2diag.log: String Title:sqleMarkDBad: PID:7980 Node:000
221
367570
Atli
by: Atli | last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t. In situations where your PHP application needs to store entire files, the preferred method is to save the file onto the server’s file-system, and store the physical location of the file in your database. This is generally considered to be the easiest and fastest way to store files. ...
5
1163
by: Jonathan Wood | last post by:
I have a Web application. Initially, I created a database in the App_Data folder and then attached it to SQL Servero 2005 (not SQL Server Express). I realize now that this is now what I want. So I created a backup of the database, detached the database file, and then created a new database from the backup that is fully a part of SQL Server 2005. The new database works just fine but I'm having the following problems: 1. I can delete...
0
9372
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9225
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9137
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9071
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5991
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4490
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4758
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2581
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2135
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.