Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nEDcN-00034S-NU for pgsql-general@arkaria.postgresql.org; Sun, 30 Jan 2022 17:03:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nEDcM-0002bG-Kb for pgsql-general@arkaria.postgresql.org; Sun, 30 Jan 2022 17:03:58 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nEDcM-0002b0-89 for pgsql-general@lists.postgresql.org; Sun, 30 Jan 2022 17:03:58 +0000 Received: from mail-qt1-x832.google.com ([2607:f8b0:4864:20::832]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nEDcK-00049y-1i for pgsql-general@lists.postgresql.org; Sun, 30 Jan 2022 17:03:57 +0000 Received: by mail-qt1-x832.google.com with SMTP id r14so9466655qtt.5 for ; Sun, 30 Jan 2022 09:03:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=pqpJAksWn8L7BHtZbJoRoeG4jJQ63ntYjk9KhLNelIE=; b=E3/HDz0okWnSLxq3y6/uIvvXBjrdsqqbv3DcsrAh/5CPklSwTlhe9DfZo8oLYDxe7y tqB5WiWFPXG3HtRmG5KhqT9wz2aIJMOHn2TyJfq1BCRuJ9OA8+BZvMGg0M1y2cUg32cC BIbCCswKmivAJcV3ebQuzWcXJJilF/ZNZQLhqBLd0EbgTaV8EfQTPeSCkAyP/6P5opav UYMvOr5SLf4z2A9I3ploQNRXSICWeXnWIlQ+mOLII1AeEEWsf1tsEsNT3K8ptasgJkgl H9UPflewHGm4mlaADA5cVVwnnPc1g/STmNDFON0qfMXbhj5LBImERP3SbSvUgINv48xb CcfA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=pqpJAksWn8L7BHtZbJoRoeG4jJQ63ntYjk9KhLNelIE=; b=PqAQ6XxQETWVWKsSL8/OD8v+awnaTMmSkZWTSJip0hN94aK7ZNRVAU3hywfsDBll16 2IVcZSt4o0qKXFj2pT2p3+bbmZlgzBoeSjE3zvPBFj89kGtruv9AJQdn28w30/BEOR0m wravotvsxGCxfZ/do7bB0pkqlULWT19k93sTQ7hez+vX85YadlNaG7hT4MCd5wFmix2E 4mANxno97VQJyaEP5ASGvVUms34PbAYyECxZKqTghkgzO/21FXPLHroJDcV7WDnECvvl 9hCwxmxn6oCHFmFzGyxwHL7Jxkrq7scNeD//zzTmoxDONWlgS/Rl/0NQ4PhTDJi+WZOo CM9Q== X-Gm-Message-State: AOAM531vjhaIxQv1GZ/lowl8gRLlTB6h+iLp1yT8f6GMkv+wJPQbBAGM 7pJIPv6OlLgwJAjcmaQuC3zRmagUciu88Q== X-Google-Smtp-Source: ABdhPJxDBWj4VMngIPxkg8w+o0O8yt6327z3AhVCJrcX41tyN205Osv6s5DVTIS0Kj0V2KEQQH1bGw== X-Received: by 2002:a05:622a:552:: with SMTP id m18mr12105017qtx.579.1643562234285; Sun, 30 Jan 2022 09:03:54 -0800 (PST) Received: from [192.168.2.100] (pool-72-79-19-93.nwrknj.east.verizon.net. [72.79.19.93]) by smtp.gmail.com with ESMTPSA id c7sm7313101qkp.98.2022.01.30.09.03.52 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 30 Jan 2022 09:03:53 -0800 (PST) Content-Type: multipart/alternative; boundary="------------ympzzGg0WxzxBJtIIqiI0i5L" Message-ID: <1f8c76b8-779f-7ea7-52e1-3a48cdfc678c@gmail.com> Date: Sun, 30 Jan 2022 12:03:52 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.5.0 Subject: Re: what is the solution like oracle DB's datafile Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Mladen Gogala In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ympzzGg0WxzxBJtIIqiI0i5L Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 1/29/22 17:15, Yudianto Prasetyo wrote: > Hello, > > I want to ask why postgresql doesn't create a datafile like it has oracle? > > I'm confused when I have 2 HDD. HDD 1 is used to install the OS and > postgresql database. when HDD 1 is full. how to increase the capacity > of postgresql database with HDD 2 (without RAID system)? > > is there any other way like oracle DB's "add datafile" which can be > used to add capacity to another HDD? > > I'm sorry if it says comparing with Oracle DB, but in essence I'm just > looking for a solution to the problem above. > > Thank You > Yours faithfully > > > Yudianto Number one, Postgres tables are files. Tablespaces are directories which reside in file systems, In Oracle, tables are sets of blocks within data files that tablespace is comprised of. That is a profound difference. In Oracle, we are using direct IO to bypass the OS cache and only use SGA ("System Global Area") for caching data blocks. In Postgres, we are caching blocks from the files in OS cache which is essentially the free memory. When translated to Postgres, your question reads: how to add space to file system? That depends on the file system and volume manager. If you're using brtfs or zfs (hopefully not) then your file sysems are also volume managers, If you're using LVM with xfs (my combination), then you can add space to your volume and extend the xfs file system. I have also tried using Oracle ASM as volume manager and use ACFS as the file system. The result was very nice, roughly the same as with LVM. However, the installation of ASM is rather complex and since PostgreSQL cannot be clustered, there is no justification for doing that. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------ympzzGg0WxzxBJtIIqiI0i5L Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
On 1/29/22 17:15, Yudianto Prasetyo wrote:
Hello,

I want to ask why postgresql doesn't create a datafile like it has oracle?

I'm confused when I have 2 HDD. HDD 1 is used to install the OS and postgresql database. when HDD 1 is full. how to increase the capacity of postgresql database with HDD 2 (without RAID system)?

is there any other way like oracle DB's "add datafile" which can be used to add capacity to another HDD?

I'm sorry if it says comparing with Oracle DB, but in essence I'm just looking for a solution to the problem above.

Thank You
Yours faithfully


Yudianto

Number one, Postgres tables are files. Tablespaces are directories which reside in file systems, In Oracle, tables are sets of blocks within data files that tablespace is comprised of. That is a profound difference. In Oracle, we are using direct IO to bypass the OS cache and only use SGA ("System Global Area") for caching data blocks. In Postgres, we are caching blocks from the files in OS cache which is essentially the free memory.

When translated to Postgres, your question reads: how to add space to file system? That depends on the file system and volume manager. If you're using brtfs or zfs (hopefully not) then your file sysems are also volume managers, If you're using LVM with xfs (my combination), then you can add space to your volume and extend the xfs file system. I have also tried using Oracle ASM as volume manager and use ACFS as the file system. The result was very nice, roughly the same as with LVM. However, the installation of ASM is rather complex and since PostgreSQL cannot be clustered, there is no justification for doing that.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------ympzzGg0WxzxBJtIIqiI0i5L--