Hi, We have a requirement to have multiple schemas in a database.Each schema will have all application tables and hold a set of users data. We have segmented data across different schemas. We dynamically increase the schemas as in when user signs up. So the table files created across all schemas resides in a single folder under data/base/16546/***. We can't find any restriction to number of relation(table,index) on a database in postgresql. But Number of files in a folder in linux is restricted in our file system what we use currently 32k/64K (ext2 / ext4). To overcome this we create a folders inside a database folder and associate as table space for each schema but it breaks the streaming replication in standby server which we use currently. To overcome this We changed the postgresql-9.4.0 source code to create a sub directory "my_<oid of tablespace>" inside the location given in create tablespace statement and take that location as tablespace location. Now we specify one common location to create multiple tablespaces, on both slave and master because postgresql creates a tablespace on subdirectory(my_<oid of tablespace>). Since I'm not an expert in postgresql, i can't assure that it will not affect other functionality of postgres. Please help me, i changed the method named "create_tablespace_directories" in a file "tablespace.c".
Here's the modified method. static void create_tablespace_directories(const char *location, const Oid tablespaceoid) { char *linkloc; char *location_with_version_dir; char * newlocation; struct stat st; linkloc = psprintf("pg_tblspc/%u", tablespaceoid); newlocation=psprintf("%s/my_%d",location,tablespaceoid); location_with_version_dir = psprintf("%s/%s", newlocation, TABLESPACE_VERSION_DIRECTORY); /* * Attempt to coerce target directory to safe permissions. If this fails, * it doesn't exist or has the wrong owner. */ if (chmod(location, S_IRWXU) != 0) { if (errno == ENOENT) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_FILE), errmsg("directory \"%s\" does not exist", location), InRecovery ? errhint("Create this directory for the tablespace before " "restarting the server.") : 0)); else ereport(ERROR, (errcode_for_file_access(), errmsg("could not set permissions on directory \"%s\": %m", location))); } ereport(WARNING,(errmsg("Trying to create : \"%s\"",newlocation))); if(mkdir(newlocation,S_IRWXU)<0) { if (errno == EEXIST) ereport(ERROR, (errcode(ERRCODE_OBJECT_IN_USE), errmsg("directory \"%s\" already in use as a tablespace", newlocation))); else ereport(ERROR, (errcode_for_file_access(), errmsg("could not create directory \"%s\": %m", newlocation))); } if (InRecovery) { /* * Our theory for replaying a CREATE is to forcibly drop the target * subdirectory if present, and then recreate it. This may be more * work than needed, but it is simple to implement. */ if (stat(location_with_version_dir, &st) == 0 && S_ISDIR(st.st_mode)) { if (!rmtree(location_with_version_dir, true)) /* If this failed, mkdir() below is going to error. */ ereport(WARNING, (errmsg("some useless files may be left behind in old database directory \"%s\"", location_with_version_dir))); } } /* * The creation of the version directory prevents more than one tablespace * in a single location. */ if (mkdir(location_with_version_dir, S_IRWXU) < 0) { if (errno == EEXIST) ereport(ERROR, (errcode(ERRCODE_OBJECT_IN_USE), errmsg("directory \"%s\" already in use as a tablespace", location_with_version_dir))); else ereport(ERROR, (errcode_for_file_access(), errmsg("could not create directory \"%s\": %m", location_with_version_dir))); } /* * In recovery, remove old symlink, in case it points to the wrong place. * * On Windows, junction points act like directories so we must be able to * apply rmdir; in general it seems best to make this code work like the * symlink removal code in destroy_tablespace_directories, except that * failure to remove is always an ERROR. */ if (InRecovery) { if (lstat(linkloc, &st) == 0 && S_ISDIR(st.st_mode)) { if (rmdir(linkloc) < 0) ereport(ERROR, (errcode_for_file_access(), errmsg("could not remove directory \"%s\": %m", linkloc))); } else { if (unlink(linkloc) < 0 && errno != ENOENT) ereport(ERROR, (errcode_for_file_access(), errmsg("could not remove symbolic link \"%s\": %m", linkloc))); } } /* * Create the symlink under PGDATA */ if (symlink(newlocation, linkloc) < 0) ereport(ERROR, (errcode_for_file_access(), errmsg("could not create symbolic link \"%s\": %m", linkloc))); pfree(linkloc); pfree(newlocation); pfree(location_with_version_dir); } ----- sudalai -- View this message in context: http://postgresql.nabble.com/File-count-restriction-of-directory-limits-number-of-relations-inside-a-database-tp5844711.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers